List columns and attributes for every table in a SQL Server database

By: Tim Ford   |   Read Comments (12)   |   Related Tips: More > Scripts

Problem
Just yesterday I had a colleague ask if I could help document all the columns in each table in one of our databases to share with a vendor working on an interface between that system and one the vendor developed for a different aspect of our business. This vendor needed to know the column names, max length, data type and whether a null value was acceptable for each of the columns in the database.  He was planning on opening each table individually within Microsoft SQL Server Management Studio (SSMS) and then document each column individually within Microsoft Excel. 

His thought: "there must be a better way!" 

He was right.  I offered up assistance and dug into the system catalog views that have been around since SQL Server 2005.  These system views complement the system compatibility views which were a replacement for direct queries against the system tables in previous releases of Microsoft SQL Server up until SQL Server 2005.  By tapping into the metadata exposed via these views, a very simple query provided everything that he needed to know.

Solution
The system catalog views used in this query are:

• sys.tables
• sys.all_columns
• sys.types

sys.tables provides one row for each table in a database.  This does include user tables and system tables that exist in each database.  There is a column within sys.tables: [is_ms_shipped] that identifies system tables.  This allows you to filter your results to just user tables if you so desire without having to join to the sys.sysobject system compatibility view's type column (where sys.sysobjects.type = 'U') in order to do so.

sys.all_columns offers a row for each column for every object in a database.  Many of the columns are shared with sys.types, and we pull most of the metadata from this view, but there is still one column lacking from our result set that we must pull from sys.types.

sys.types is the catalog view that stores rows relating to system and user-defined data types and their properties.  The only field from this view we will need is the data type's name, as it is the only field in our desired result set we can't return from sys.all_columns as it pertains to column metadata.

Each of the catalog views are scoped at the database level, meaning that they exist in each database (system or user) on the SQL Server instance.  Even sys.types, a catalog view you may expect to be the same across all databases on an instance is scoped at the database level.  Why?  Quite simply put, sys.types includes user-defined datatypes that are unique to a database.  Database collation also has an impact on sys.types, therefore making native data types such as text, ntext, varchar(), char(), nvarchar(), nchar(), and sysname different between databases if their collations differ. 

Because each of our system catalog views we're using is scoped to the database level I strongly suggest that you run it from within the context of the database you're attempting to document.  The first example below does just that, returning all columns for all user tables in the database.

Here is the output if you run this against the Northwind database.

If, for some reason, you must run the query from a database context that is not the one you wish to document you can use the following code and still return the same results.  As you can see you need to hardcode the database name. In this example the Northwind database is hardcoded for each of the system tables.  It works, but not recommended.

As you can see you must use fully-qualified naming conventions for the objects and also supply the database name as a parameter for the DB_ID() function.

Using the first query I was able to return the results, drop them into an Excel spreadsheet, and provide them to the developer and his vendor contact to satisfy their needs without the manual work he was dreading that was to come.

Next Steps

• Need to determine which columns in your database include default values.  If so, here is a previous tip to satisfy that need.
• You might ask yourself why not just use the INFORMATION_SCHEMA.COLUMNS system view to get this information.  The simple answer is you can, but it combines information for both tables and views.
• Next tip in this series focuses on documentation of all index columns in your database.  Stay tuned!

Last Update: 6/26/2009

About the author

Tim is a Senior Database Administrator and SQL Server MVP. He has been working with SQL Server since 1999.

View all my tips

We Recommend

• More Database Developer Tips...

Comments and Feedback:

Friday, June 26, 2009 - 7:23:47 AM - tranfamily35	Read The Tip
Wouldn't you want to use the built in INFORMATION_SCHEMA.COLUMNS?

Friday, June 26, 2009 - 7:25:06 AM - aprato	Read The Tip
Hi Tim  I think the INFORMATION_SCHEMA.COLUMNS view is still a good option as well if filtered on just on tables i.e. select * from information_schema.columns  where objectproperty(object_id(table_name),'IsTable') = 1 order by table_name, ordinal_position

Monday, June 29, 2009 - 5:48:43 AM - spikelly	Read The Tip
HI ! Thank you for this script. It works really fine! But what if I want to include the tables and colums description (extended properties) ? Thanks in advance for your reply.

Monday, June 29, 2009 - 6:38:15 AM - aprato

Read The Tip

Using the original query in the tip: SELECT OBJECT_SCHEMA_NAME(T.[object_id],DB_ID()) AS [Schema],           T.[name] AS [table_name], AC.[name] AS [column_name],           TY.[name] AS system_data_type, AC.[max_length],         AC.[precision], AC.[scale], AC.[is_nullable], AC.[is_ansi_padded], p.value FROM sys.[tables] AS T     INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id]  INNER JOIN sys.[types] TY ON AC.[system_type_id] = TY.[system_type_id] AND AC.[user_type_id] = TY.[user_type_id]   LEFT OUTER JOIN  sys.extended_properties p    ON  p.major_id = AC.object_id    AND p.minor_id = AC.column_id WHERE T.[is_ms_shipped] = 0 ORDER BY T.[name], AC.[column_id]

Monday, June 29, 2009 - 7:45:44 AM - spikelly	Read The Tip
Works great ! Thank you !

Tuesday, August 25, 2009 - 1:23:28 PM - lamatt	Read The Tip
Just wanted to thank you for this post.  This query returned 91,616 lines to document my DB.  Any ideas on how to add to this to return info on tables that are NOT populated?

Tuesday, August 25, 2009 - 2:17:54 PM - aprato

Read The Tip

Not clear on what you're driving at but is this what you're looking for?  SELECT OBJECT_SCHEMA_NAME(T.[object_id],DB_ID()) AS [Schema],           T.[name] AS [table_name], AC.[name] AS [column_name],           TY.[name] AS system_data_type, AC.[max_length],         AC.[precision], AC.[scale], AC.[is_nullable], AC.[is_ansi_padded], p.value FROM sys.[tables] AS T     INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id]  INNER JOIN sys.[types] TY ON AC.[system_type_id] = TY.[system_type_id] AND AC.[user_type_id] = TY.[user_type_id]   inner join sys.partitions sp on t.[object_id] = sp.[object_id] and sp.index_id in (0,1) and sp.rows = 0 LEFT OUTER JOIN  sys.extended_properties p    ON  p.major_id = AC.object_id    AND p.minor_id = AC.column_id WHERE T.[is_ms_shipped] = 0 ORDER BY T.[name], AC.[column_id]

Wednesday, December 02, 2009 - 5:40:49 PM - nosnetrom	Read The Tip
"Next tip in this series focuses on documentation of all index columns in your database. Stay tuned!" This was a great tip for a SQL n00b! Looking forward to the tip on index columns!

Thursday, June 10, 2010 - 9:14:16 AM - GaryMazzone	Read The Tip
I actually wrote a .Net application that will display this information (and other things... all indexes and their fields, Parent tables and Child tables with the fields involved, View that use the table, Constraints on the field, procs and function that touch the table)   Some other stuff also.

Monday, June 11, 2012 - 4:12:41 AM - Shoaib	Read The Tip
Im Fresher in sql , i have a proble in a query .... i wnat a query to get schema and insert data both  as in sql query builder makes a both schema and data please help me..

Tuesday, August 07, 2012 - 10:04:43 AM - gitmo	Read The Tip
Tim, Your article is quite informative and clearly written.  Thank you.

Tuesday, August 07, 2012 - 12:53:40 PM - Ralph Wilson

Read The Tip

@GaryMazzone, Would your script allow the retrieval of the server, database, schema, and table information for multiple servers with (potentially) multiple instances per server and insert the information into a table in a designated database (i.e. a single table in a specific database on one server)?  I have a need for that and, while I have established a way to create a single database and table on _each_ server/instance, with the result that I then have to consolidate the data after it is extracted; however, it would be much nicer to be able to consolidate it in one pass and, for instance, do so in a separate databse designated for that purpose. @Shoaib,  I'm not sure I understand what you want to achieve.  The provided query pulls the schema as well as the table/column information.  So, what else are you trying to achieve?

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates Signup for our newsletter

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code

 

Sponsor Information

SQL Server having some performance issues? Idera SQL check. FREE SQL Server enhancement. SQL Developer Bundle: Cut out dull work with 12 tools for simpler, faster database development. Free trial Is “blocking” a bad word at your company? Contact the Edgewood SQL Server Consultants for the resolution. Unlock the power of the Transaction log to discover unauthorized changes and recover lost data The SQL Server Security THREAT - It’s Closer Than You Think