Syntax in sqlserver - find the columns for a table

[krotha]

Can some one tell me the syntax in sql server to find out all the tables and views in my database.
I use select * from tab in oracle. I need like this in sqlserver.

secondly to find out all the columns in a particular table.
I use the select statement in oracle like this.
select column_name from all_tab_columns
where table_name ='EMP'

Thanks in advance for your help.

 

[tlbroadbent]

List tables:

Select * from information_schema.tables
where TABLE_TYPE='base_table'

List columns in a table:

Select * from information_schema.columns
where table_name='tablename' Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[krotha]

Thanks a lot Terry .

 

[krotha]

Can you please tell me the syntax for MS Access also.

Thanks for your help.

 

[tlbroadbent]

In Access use the following.

Select * from msysobjects
Where Type=<type>

<type>
1 = Table
3 = Query
4 & 5 = Linked tables Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[krotha]

Thanks Terry. what about my second query to find the columns for a particular table. Where can I get information about this to read. Ms access Help doesn't useful much.

Thanks a lot.

 

[tlbroadbent]

Check thread181-138446. Quehay provided a module for documenting an Access database. It is an excellent tool. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.