Find User Tables and Their Columns Info in SQL Server Using Object Catalog Views (Repost)
Marlon Ribunal's Note On SQL Server
[Update 07-09-09: Added MSDN Link at the bottom]
Here’s a quick TSQL solution that you can use to find all the User Tables and their Columns, inluding Data Types, and Column size. This is useful when you need a quick way of finding info on creating your Database’s documentation. Whenever I am asked to document a new system/application or review an existing one, I always want to start from the very core of the system – mostly, this is a Database backend.
SELECT t.name AS [TABLE Name],
c.name AS [COLUMN Name],
p.name AS [DATA Type],
p.max_length AS[SIZE],
CAST(p.PRECISIONASVARCHAR)
+'/'
+ CAST(p.scale ASVARCHAR) AS [PRECISION/Scale]
FROMsys.objectsAS t
JOINsys.columnsAS c
ON t.OBJECT_ID=c.OBJECT_ID
JOINsys.typesAS p
ON c.system_type_id=p.system_type_id
WHERE t.type_desc='USER_TABLE';
UPDATE: MSDN LINK
Querying the SQL Server System Catalog FAQSQL Server 2005 | SQL…
View original post 4 more words