Tuesday, March 3, 2009

SQL query to fetch all the tables and column names from the database

select table_name, column_name, data_type, character_maximum_length, is_nullable 
from information_schema.columns 
where table_name in (select name from sysobjects where xtype='U') order by table_name

Here xtype='U' means user table.

Some of the other options you can use are,
C: Check constraint
D: Default constraint
F: Foreign Key constraint
L: Log
P: Stored procedure
PK: Primary Key constraint
RF: Replication Filter stored procedure
S: System table
TR: Trigger
U: User table
UQ: Unique constraint
V: View
X: Extended stored procedure
Press F5 and see the result!!!!!!

No comments: