Finding All User Tables and Their Columns & Data Types In SQL Server Using Object Catalog Views

marl — Mon, 16 Jun 2008 04:09:00 GMT

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.PRECISION AS VARCHAR) +'/'+ CAST(p.scale AS VARCHAR) AS [PRECISION/Scale]
FROM sys.objects AS t
JOIN sys.columns AS c
ON t.OBJECT_ID=c.OBJECT_ID
JOIN sys.types AS p
ON c.system_type_id=p.system_type_id
WHERE t.type_desc='USER_TABLE';

-Marlon Ribunal

[Cross-Posted From My Other Blog http://dbalink.wordpress.com]

αρχάριος : SQL Servererver Object Catalog Views

Finding All User Tables and Their Columns & Data Types In SQL Server Using Object Catalog Views