List all columns, from a database

,

This script will list all columns, their datatypes, and sizes from any database.  It can be very useful for contractor DBAs who need to quickly scan all tables, and very helpful for developers, or when you just need a quick list of fields for a meeting.

SELECT DISTINCT 
    LEFT(a.name, 30) AS fieldname, left(c.name,20) AS datatype, 
    a.length AS size, b.name AS tablename
FROM syscolumns a INNER JOIN
    systypes c ON a.xtype = c.xusertype INNER JOIN
    sysobjects b ON a.id = b.id
WHERE (b.xtype = 'U') AND (b.name <> 'dtproperties')
ORDER BY size desc, tablename, fieldname

Rate

5 (1)

Share

Share

Rate

5 (1)