INFORMATION_SCHEMA.COLUMNS in SQL Server

Geeking with INFORMATION_SCHEMA.

Information schema views are very handy. Books online describes these views as follows:

Information schema views provide an internal, system table-independent view of the SQL Server metadata. Information schema views enable applications to work correctly although significant changes have been made to the underlying system tables. The information schema views included in SQL Server comply with the ISO standard definition for the INFORMATION_SCHEMA.

So when you want to get some information about your tables, such as names, data types, constraints to name a few, go check out INFORMATION_SCHEMA in Books Online.

I’m always whipping up stuff like the following when using the INFORMATION_SCHEMA views as you probably do, and especially find the COLUMNS view cool for extracting metadata.  For example, the following script was for a technical specification document at work where I wanted some but not all of the table DDL. (No, we didn’t put the AdventureWorks SalesTaxRate table in the document…just using it as an example for this post :D)

USE AdventureWorks
SELECT 
        '['+COLUMN_NAME+']' as Column_name
        ,DATA_TYPE +  CASE WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN 
                        ' ('+ CAST(CHARACTER_MAXIMUM_LENGTH AS varchar (3))+ ')'
                      ELSE '' END AS Data_Type
        ,IS_NULLABLE AS Allows_Nulls         
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'SalesTaxRate'

Works very well in a word document; create a table with rows and columns in Microsoft Word and then copy and paste the result.  But, I ran into this one today:

Wanna guess what the data type was?  Yep, it was a varchar (max). But just don’t try to create a table with a varchar (-1) because it won’t work.  I know because I tried.

How about creating a list of comma-separated columns from a table:

DECLARE @string varchar (max)=''

SELECT @string = @string + ','+ COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Vendor'

SELECT RIGHT(@string, LEN(@STRING)-1)

I’ve always enjoyed a script like this, where you are essentially ‘cramming’ the result set into a variable, looping sort of, with no looping construct.  Note that I set it equal to an empty string – if you didn’t, the NULL default would not concatenate to the result, and you’d have to use SET_CONCAT_NULL_YIELDS_NULL OFF in order to make it work. 

Finally, remember that INFORMATION_SCHEMA works for the database that you are connected to in a current query window, so if you are in the AdventureWorks database and want the columns from a table called Orders in the Test database, you’ll need to hit CTL + U and change the context, use a USEing clause and switch over, or do the following:

SELECT *
FROM Test.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Orders'

Thanks for reading,

Lee

----------------------

Elaine: Would you gentlemen care to order your dinners?
First Jive Dude: Bet, babe. Slide a piece o' da' porter. Drinks, I run da' java.
Second Jive Dude: Lookie here. I can dig grease 'n chompin' on some buns and draggin' through the garden.