Debunking the Myths: Cloud HA and DR common misconceptions

Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips
































Top SQL Server Tools



































   Got a SQL tip?
            We want to know!

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

MSSQLTips author Tim Ford By:   |   Read Comments (13)   |   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.

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_typeAC.[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] 
ORDER BY T.[name]AC.[column_id]

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.

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_typeAC.[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] 
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!


Last Update: 6/26/2009


About the author
MSSQLTips author Tim Ford
Tim Ford is a Senior Database Administrator and SQL Server MVP. He has been working with SQL Server since 1999.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server 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?


Thursday, August 29, 2013 - 5:20:10 PM - Alex Read The Tip

Great job! Thank you!



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



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.