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

By:   |   Comments (20)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | 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.

USE [Northwind]; 
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]  
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]   
WHERE T.[is_ms_shipped] = 0  
ORDER BY T.[name], AC.[column_id]

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

schema

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.

USE [master]; 
SELECT OBJECT_SCHEMA_NAME(T.[object_id],DB_ID('Northwind')) 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]  
FROM Northwind.sys.[tables] AS T   
 INNER JOIN Northwind.sys.[all_columns] AC ON T.[object_id] = AC.[object_id]  
 INNER JOIN Northwind.sys.[types] TY ON AC.[system_type_id] = TY.[system_type_id] AND AC.[user_type_id] = TY.[user_type_id]   
WHERE T.[is_ms_shipped] = 0  
ORDER BY T.[name], AC.[column_id] 

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!


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Tim Ford Tim Ford is a Senior Database Administrator with MindBody.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Wednesday, April 29, 2020 - 3:01:04 AM - Vinay Salian Back To Top (85498)

How to add the constraints like foriegn key and primary key


Thursday, October 25, 2018 - 1:55:37 AM - Mark Back To Top (78051)

 THANK YOU!  I used the first query......

ran with no problems and produced the results I wanted.

 


Monday, August 14, 2017 - 3:48:13 PM - Michael Back To Top (64824)

 Is there a where clause that can exclude tables that return 0 rows?


Friday, May 12, 2017 - 9:01:14 PM - foo Back To Top (55707)

what if sys.tables is not populated?

 

 

 


Tuesday, March 29, 2016 - 4:18:46 PM - Jim Coburn Back To Top (41089)

 Thanks.  This worked like a charm.

 


Tuesday, December 9, 2014 - 11:05:36 AM - Romana Back To Top (35551)

Thank you!


Saturday, September 20, 2014 - 11:08:21 AM - Todd Back To Top (34640)

Now what I need is the ability to pull the details of a single field from a single table (Data type, Data max-length, etc) *AND* the VALUE of that field from that table, from one particular row, as in "where ItemID = @ItemID"  But without knowing the field name passed in.


Thursday, August 29, 2013 - 5:20:10 PM - Alex Back To Top (26534)

Great job! Thank you!


Tuesday, August 7, 2012 - 12:53:40 PM - Ralph Wilson Back To Top (18944)

@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?


Tuesday, August 7, 2012 - 10:04:43 AM - gitmo Back To Top (18942)

Tim,

Your article is quite informative and clearly written.  Thank you.


Monday, June 11, 2012 - 4:12:41 AM - Shoaib Back To Top (17921)

 

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..


Thursday, June 10, 2010 - 9:14:16 AM - GaryMazzone Back To Top (5678)

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.


Wednesday, December 2, 2009 - 5:40:49 PM - nosnetrom Back To Top (4513)
"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!

Tuesday, August 25, 2009 - 2:17:54 PM - aprato Back To Top (3957)

 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]


Tuesday, August 25, 2009 - 1:23:28 PM - lamatt Back To Top (3956)

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?


Monday, June 29, 2009 - 7:45:44 AM - spikelly Back To Top (3658)

Works great !

Thank you !


Monday, June 29, 2009 - 6:38:15 AM - aprato Back To Top (3657)

 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 - 5:48:43 AM - spikelly Back To Top (3656)

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.


Friday, June 26, 2009 - 7:25:06 AM - aprato Back To Top (3650)

 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


Friday, June 26, 2009 - 7:23:47 AM - tranfamily35 Back To Top (3649)

Wouldn't you want to use the built in INFORMATION_SCHEMA.COLUMNS?















get free sql tips
agree to terms