INFORMATION_SCHEMA.COLUMNS


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

n1

n2

n3

n4

n5

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

<< Previous

Next >>



Comments For This Article




Friday, July 21, 2023 - 9:26:27 AM - Eric Gwozda Back To Top (91419)
Hi,
Is there a way to use INFORMATION_SCHEMA.COLUMNS to only bring back the metadata for views, not tables. Or is there a better way to get view names and view column names?

Thanks

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

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 (45210)

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 (45202)

 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 (40803)

 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 4, 2016 - 2:55:02 AM - charles theman Back To Top (40590)

 Hello,

 

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

 

Thank you.

 

Monday, February 1, 2016 - 1:42:38 AM - SHWETA SHUKLA Back To Top (40560)

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? 














get free sql tips
agree to terms