Returning Column Metadata for a Specific Table

Yesterday morning, I sat down with my cup of fresh brewed coffee, glanced around my recently cleaned office (a major feat that I'd been working on for weeks), and fired up Twitter while trying to determine where to expend energy now that I had tackled the DIRTY_OFFICE SQL wait. If I'm anything like many of you, it takes me a while to get my brain going first thing in the morning. Let me rephrase that, it takes a while to get my brain focused down the right path first thing in the morning. (It's rather easy for me to start down multiple wrong paths first thing!) Twitter was where inspiration for this article—and for warming up my creative juices—originated.

One of my fellow Microsoft MVPs, Pieter Vanhove (@Pieter_Vanhove), had included a link of mine from an earlier article on collecting column information for all columns in a database for David Hill (@Oka_Bi_David), who had originally posted the following tweet using the #sqlhelp hashtag . . . the Twitter SOS for SQL Questions:

The article David was pointed to was a general script for searching for all columns matching a specific name, not data type details for all the columns of a specific table (which ended up being what he was looking for). I got involved because I was included on a retweet along the way. I figured I had a bit of time before the work day started so why not step in, solve David's problem, and take care of a responsibility for an article, all at the same time. As the old addage says: "Measure twice—cut once," right?

Returning Column Data Type Metadata for All Columns on a Table

At the heart of returning this information, is the relationship for three system catalog views:

• sys.tables provides information relating to the tables in a SQL Server database
• sys.columns is the source for column metadata in your databases and is related to sys.tables by object_id, the unique identifier for any SQL Server object
• sys.types provides datatype metadata and is related to sys.columns on both system_type_id and user_type_id

Using these views, and the relationships above, I was able to provide David with the following query:

SELECT OBJECT_SCHEMA_NAME(T.object_id) AS schema__name
    , T.name AS table__name
    , C.name AS column__name
    , C.is_identity
    , Y.name AS data__type
    , C.is_nullable
    , C.max_length
    , C.precision
    , C.scale
    , C.collation_name
    , Y.is_user_defined        
    , C.is_ansi_padded
    , C.is_rowguidcol
    , C.is_computed    
FROM sys.tables T 
    INNER JOIN sys.columns C 
        ON T.object_id = C.object_id
    INNER JOIN sys.types Y 
        ON C.system_type_id = Y.system_type_id 
            AND C.user_type_id = Y.user_type_id
WHERE T.name = ''
ORDER BY C.column_id;

Please note that when it comes to writing code I'll re-use, I like to follow the same guides I used when coming up with the idea for the post you're reading: do it once and re-use often. That's why you'll see the template parameter for the WHERE clause. If you read, Using the all_columns System View in SQL Server, you'll see how powerful those can be. Simply use the keystroke combination of Ctrl+Shift+M to replace that parameter with the table name you're targeting, and you're all set.

For the sake of showing the output I'll run this substituting in a table from AdventureWorks:

SELECT OBJECT_SCHEMA_NAME(T.object_id) AS schema__name
    , T.name AS table__name
    , C.name AS column__name
    , C.is_identity
    , Y.name AS data__type
    , C.is_nullable
    , C.max_length
    , C.precision
    , C.scale
    , C.collation_name
    , Y.is_user_defined        
    , C.is_ansi_padded
    , C.is_rowguidcol
    , C.is_computed    
FROM sys.tables T 
    INNER JOIN sys.columns C 
        ON T.object_id = C.object_id
    INNER JOIN sys.types Y 
        ON C.system_type_id = Y.system_type_id 
            AND C.user_type_id = Y.user_type_id
WHERE T.name = 'StateProvince'
ORDER BY C.column_id;

In this case, the script is also quite flexible because there are a plethora of columns in these views that can be mined depending upon your circumstances and needs—all with the same relationship at the core of the query.

Yes, you could also get this information in a less-user friendly way using sp_help, but the output data would not be as easily usable for subsequent processing. In the end, that is what David was looking to use it for—a feeder into a larger analysis of data types and schema changes. It's always nice to start the day with a win and help out the SQL Community all at the same time. 

If you have a question about SQL Server and you've done a thorough Bing/Google search with no success, then use the #sqlhelp hashtag on Twitter and you might get a quick solution to your problem—perhaps even something custom-built, if you're lucky!