Retreiving all table structures in a database in SQL Server

In order to retrieve the structure of all tables in a database i.e., retrieving the table names with the column names and their corresponding data size, below query can be used….

SP_MSFOREACHDB
'
  IF ''?'' NOT IN (''master'',''tempdb'',''msdb'',''model'',''northwind'',''pubs'')
  BEGIN
    USE ?
    PRINT ''?''
    SELECT 
         @@SERVERNAME
       , ''?''
       , A.NAME AS TABLE_NAME
       , B.NAME as COLUMN_NAME
       , C.NAME as DATATYPE
       , B.LENGTH
       , B.XPREC as [PRECISION]
       , B.XSCALE AS SCALE
  FROM SYSOBJECTS A
  JOIN SYSCOLUMNS B
    ON A.ID=B.ID
  JOIN SYSTYPES C
   ON B.XUSERTYPE=C.XUSERTYPE
 WHERE A.type=''u'' 
    AND A.NAME NOT LIKE ''dtproperties''
 ORDER BY A.NAME
 END
'

In order to retreive structure of a particular table in a database, use the below query:

SELECT
    OBJECT_NAME(A.ID) TABLE_NAME
  , B.NAME COL_NAME
  , C.NAME DATA_TYPE
  , B.LENGTH
  , B.XPREC [PRECISION]
  , B.XSCALE AS SCALE
FROM SYSOBJECTS A
JOIN SYSCOLUMNS B
  ON A.ID=B.ID
JOIN SYSTYPES C
  ON B.XTYPE=C.XTYPE
WHERE A.XTYPE='U'
  AND A.NAME LIKE 'SEP_WSLVASComponent'
  AND C.NAME NOT LIKE 'SYSNAME'
Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: