Skip to content

Find User Tables and Their Columns Info in SQL Server Using Object Catalog Views

June 15, 2008
[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.PRECISION AS VARCHAR)
+'/'
CAST(p.scale AS VARCHARAS [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';

UPDATE: MSDN LINK

Querying the SQL Server System Catalog FAQ
SQL Server 2005 | SQL Server 2008


-Marlon Ribunal
kick it on DotNetKicks.com

About these ads

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

  • Archives



  • RSS SQLServerPedia

  • RSS SQL Crunch – Hot SQL Links

  • My Bookmarks

  • Business Career DBA Tool Events free ebook Humor IT News Members Only Microsoft MySQL Operating System Oracle Personal Reporting Services SQL Server SQLServerPedia Syndication SSC SSIS TSQL Tutorial Uncategorized Video


  • Software Blogs - BlogCatalog Blog Directory
  • Follow

    Get every new post delivered to your Inbox.

    Join 28 other followers

    %d bloggers like this: