Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

INFORMATION_SCHEMA.COLUMNS



By:

Overview
The INFORMATION_SCHEMA.COLUMNS view allows you to get information about all columns for all tables and views within a database. By default it will show you this information for every single table and view that is in the database.

Explanation
This view can be called from any of the databases in an instance of SQL Server and will return the results for the data within that particular database.

The columns that this view returns are as follows:

Column name Data type Description
TABLE_CATALOG nvarchar(128) Table qualifier.
TABLE_SCHEMA nvarchar(128) Name of schema that contains the table.
TABLE_NAME nvarchar(128) Table name.
COLUMN_NAME nvarchar(128) Column name.
ORDINAL_POSITION int Column identification number.

Note: In SQL Server 2005, these column IDs are consecutive numbers.

COLUMN_DEFAULT nvarchar(4000) Default value of the column.
IS_NULLABLE varchar(3) Nullability of the column. If this column allows for NULL, this column returns YES. Otherwise, NO is returned.
DATA_TYPE nvarchar(128) System-supplied data type.
CHARACTER_MAXIMUM_LENGTH int Maximum length, in characters, for binary data, character data, or text and image data.

-1 for xml and large-value type data. Otherwise, NULL is returned. For more information, see Data Types (Transact-SQL).

CHARACTER_OCTET_LENGTH int Maximum length, in bytes, for binary data, character data, or text and image data.

-1 for xml and large-value type data. Otherwise, NULL is returned.

NUMERIC_PRECISION tinyint Precision of approximate numeric data, exact numeric data, integer data, or monetary data. Otherwise, NULL is returned.
NUMERIC_PRECISION_RADIX smallint Precision radix of approximate numeric data, exact numeric data, integer data, or monetary data. Otherwise, NULL is returned.
NUMERIC_SCALE int Scale of approximate numeric data, exact numeric data, integer data, or monetary data. Otherwise, NULL is returned.
DATETIME_PRECISION smallint Subtype code for datetime and SQL-92 interval data types. For other data types, NULL is returned.
CHARACTER_SET_CATALOG nvarchar(128) Returns master. This indicates the database in which the character set is located, if the column is character data or text data type. Otherwise, NULL is returned.
CHARACTER_SET_SCHEMA nvarchar(128) Always returns NULL.
CHARACTER_SET_NAME nvarchar(128) Returns the unique name for the character set if this column is character data or text data type. Otherwise, NULL is returned.
COLLATION_CATALOG nvarchar(128) Always returns NULL.
COLLATION_SCHEMA nvarchar(128) Always returns NULL.
COLLATION_NAME nvarchar(128) Returns the unique name for the collation if the column is character data or text data type. Otherwise, NULL is returned.
DOMAIN_CATALOG nvarchar(128) If the column is an alias data type, this column is the database name in which the user-defined data type was created. Otherwise, NULL is returned.
DOMAIN_SCHEMA nvarchar(128) If the column is a user-defined data type, this column returns the name of the schema of the user-defined data type. Otherwise, NULL is returned.
DOMAIN_NAME nvarchar(128) If the column is a user-defined data type, this column is the name of the user-defined data type. Otherwise, NULL is returned.

(Source: SQL Server 2005 Books Online)


Here is an example of data that was pulled from the AdventureWorks database.  This data was pulled using this query:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS

To be able to show the output the results were broken into multiple pieces.


To query for just one table you can use a query like this:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME =
'Address'

Last Update: 7/2/2009




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Monday, August 13, 2018 - 11:15:12 AM - Ken Sturgeon Back To Top

Have you ever encountered a difference in which  "SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME ='tableName'" returns the results in order other than by ORDINAL_POSITION?

I have a stored procedure that uses BCP queryout to select the column headers and all of a sudden it started returning them in a different order. I initially suspected that it was caused by restarting the SQL Service but I don't really think that's the case.

 

SET @command = 'bcp "DECLARE @columns VARCHAR(MAX); SELECT @columns = COALESCE(@columns + CHAR(9), '''') + column_name FROM ' + @dbName + '.INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = ''' + @tableName + '''; SELECT @columns;" queryout "' + @outputFileName + '.hdr" -c -r \n -T';
EXEC master..xp_cmdshell @command, no_output;

 I have since added an ORDER BY clause but our client wants to know why the column orders came back differently than they had previously.

Thank you very much for any insight you can offer.

Ken Sturgeon


Wednesday, January 11, 2017 - 7:43:48 AM - Greg Robidoux Back To Top

Hi Vivek, the information in the table above probably came from SQL Server books online.

Take a look at this: http://stackoverflow.com/questions/887370/sql-server-extract-table-meta-data-description-fields-and-their-data-types

There are a lot of good examples and different queries you can use.

-Greg


Tuesday, January 10, 2017 - 9:20:47 PM - Vivek Koppula Back To Top

 Hi Greg,

Thanks for the information. I am just wondering, how can we get the meta data about the table itself. That is how did you get the information that you presented in the table. 

Thanks and REgards

Vivek


Friday, February 26, 2016 - 11:05:51 AM - pawan Back To Top

 got working code at http://sourcecodehub.com/article/432/sql-query-script-to-get-the-columns-with-datatype-of-tables-with-default-values

 


Thursday, February 04, 2016 - 2:55:02 AM - charles theman Back To Top

 Hello,

 

How do we sort the order of the fields to be on alphabetical order on mssql 2008 r2?

 

Thank you.

 


Monday, February 01, 2016 - 1:42:38 AM - SHWETA SHUKLA Back To Top

HOW TO MODIFY A COLUMN SIZE, IF WE HAVE MULTIPLE TABLES WITH DIFFERENT SUFFIX AND THAY ALL CONTAINS SAME COLUMN DEFINATIONS, I NEED TO MODIFY A SPECIFIC COLUMN'S SIZE OF ALL TABLES USING ONE SCRIPT. 

AS LIKE MENTIONED BELOW:

UPDATE 

INFORMATION_SCHEMA.COLUMNS

SET 

CHARACTER_MAXIMUM_LENGTH = 100,

CHARACTER_OCTET_LENGTH = 100

WHERE 

TABLE_NAME LIKE '__MYTABLENAME'

AND 

COLUMN_NAME = 'MYCOLUMNNAME'

AND 

CHARACTER_MAXIMUM_LENGTH < 100

 

ANY SUGGESTION? 


Learn more about SQL Server tools