Technical Article

Retreiving all the Table Information from SQL

,

The Query will enable you to retrieve all the Tables in the Database .

The Output of the Query will list all the User Tables along with the Column Names and the Sort order of the Columns .The Query will also return as to whether the Columns in the table are with allow null option or not.

This script is very useful for obtaining the information of the Database.

/* 
Script for retrieving the list of Tables from the Database 
Author :Suresh Ramakrishnan

*/*********************************************************

Select
'Table Name ' = B.name,
'Column name' = A.name,
'Column No'   = colid,
'Type'        = type_name (xusertype),
'Length'      = convert (int, length),
'Allow Nulls'= case when isnullable = 0 then 'No' else 'Yes' end
From syscolumns A, sysobjects B
Where A.id=B.id and B.type ='U' and A. number=0 and B.Status > 0 
Order by b.name, colid

*********************************************************

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating