Using the SQL Server INFORMATION_SCHEMA view

I recently answered a posting asking how to get the columns from the primary key of a table along with the column's data type information. It turns out to be pretty easy to find. Everything needed is in the INFORMATION_SCHEMA views.

If you're unfamiliar with them, they're worth getting to know. INFORMATION_SCHEMA is a special owner name for a group of views that are available in all databases that follow the SQL-92 standard.

The information in INFORMATION_SCHEMA comes from system tables. The advantage of using INFORMATION_SCHEMA views instead of going directly to system tables is that INFORMATION_SCHEMA views won't change in new releases of SQL Server but the system tables may be different, breaking pre-existing code.

The following UDF is called udf_Tbl_PKeyColumnsTAB. It returns the columns in the primary key along with data type information. Here's the CREATE FUNCTION script:

 /------------- Copy below this line ---------------------- CREATE FUNCTION dbo.udf_Tbl_PKeyColumnsTAB ( @Tbl_Name sysname = NULL -- Get the P Key columns -- for this table. NULL for all ) RETURNS TABLE /* * Returns a table of information about the columns in a table's * primary key. (Null table name for all tables) * * Common Usage: select * from udf_Tbl_PKeyColumnsTAB(NULL) -- all pkeys * * © Copyright 2002 Andrew Novick http://www.NovickSoftware.com * You may use this function in any of your SQL Server databases * including databases that you sell,

    Requires Free Membership to View

so long as they contain * other unrelated database objects. You may not publish this * UDF either in print or electronically. ***************************************************************/ AS RETURN SELECT TOP 100 PERCENT WITH TIES tc.TABLE_NAME , kcu.COLUMN_NAME , kcu.ORDINAL_POSITION -- Position in the key , c.DATA_TYPE , c.CHARACTER_MAXIMUM_LENGTH , c.CHARACTER_SET_NAME -- typically iso_1 or Unicode , c.COLLATION_NAME -- Case/Accent Sensitivity etc. , c.NUMERIC_PRECISION -- Digits of data , c.NUMERIC_SCALE -- places to right of decimal , c.DATETIME_PRECISION FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu ON tc.TABLE_CATALOG = kcu.TABLE_CATALOG AND tc.TABLE_SCHEMA = kcu.TABLE_SCHEMA AND tc.TABLE_NAME = kcu.TABLE_NAME AND tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.[COLUMNS] c ON tc.TABLE_CATALOG = c.TABLE_CATALOG AND tc.TABLE_SCHEMA = c.TABLE_SCHEMA AND tc.TABLE_NAME = c.TABLE_NAME AND kcu.COLUMN_NAME= c.COLUMN_NAME WHERE tc.CONSTRAINT_TYPE = 'PRIMARY KEY' AND (@Tbl_Name is NULL OR tc.TABLE_NAME = @Tbl_Name) AND tc.TABLE_NAME != 'dtproperties' ORDER BY tc.TABLE_NAME , kcu.ORDINAL_POSITION GO GRANT SELECT ON dbo.udf_Tbl_PKeyColumnsTAB TO PUBLIC GO ------------ Stop copying above this line --------------------/

Usually, the name of the table for which you want primary key information is specified as the parameter to the function. However, if NULL is used as the parameter, the primary key columns from all tables is returned. This is accomplished in the WHERE clause with the expression:

 (@Tbl_Name is NULL OR tc.TABLE_NAME = @Tbl_Name)

When NULL is supplied, the expression matches every table. But when at table name is given, the primary key from only one table is returned. You should note that if the table doesn't have a primary key, nothing is returned for the table. The code invoking the function has to be prepared for this possibility. Also, note that there's no way to specify the table owner as a parameter. That's because I always have all tables owned by dbo. This function is written based on the assumption that any table not owned by dbo has been "fixed" so the situation doesn't exist. If you allow tables not owned by dbo, you'll have to modify the function to take the owner as an additional parameter and to return the tc.TABLE_SCHEMA column. Check out one of my previous functions that locates all the tables that are not owned by dbo.

Let's try udf_Tbl_PKeyColumnsTAB in Pubs. This script assumes that the CREATE FUNCTION script has already been run in pubs.

 /------------- Copy below this line ---------------------- USE pubs -- assumes udf_Tbl_PKeyColumnsTAB has been created GO SELECT COLUMN_NAME as COL , ORDINAL_POSITION as POS , DATA_TYPE , CHARACTER_MAXIMUM_LENGTH as [LEN] FROM udf_Tbl_PKeyColumnsTAB ('sales') GO ------------ Stop copying above this line --------------------/

Here are the results:

 COL POS DATA_TYPE LEN --------- ----------- ----------- ----------- stor_id 1 char 4 ord_num 2 varchar 20 title_id 3 varchar 6

To get information on all primary keys, use NULL as the parameter as in this query:

 /------------- Copy From Below this line ---------------------- USE pubs -- assumes udf_Tbl_PKeyColumnsTAB has been created GO SELECT TABLE_NAME as [TABLE] , COLUMN_NAME as [COLUMN] , ORDINAL_POSITION as POS , DATA_TYPE , CHARACTER_MAXIMUM_LENGTH as [LEN] FROM udf_Tbl_PKeyColumnsTAB (NULL) GO ------------ Stop copying above this line --------------------/


 TABLE COLUMN POS DATA_TYPE LEN -------------- ---------- ----------- ----------- ----------- authors au_id 1 varchar 11 employee emp_id 1 char 9 jobs job_id 1 smallint NULL pub_info pub_id 1 char 4 publishers pub_id 1 char 4 sales stor_id 1 char 4 sales ord_num 2 varchar 20 sales title_id 3 varchar 6 stores stor_id 1 char 4 titleauthor au_id 1 varchar 11 titleauthor title_id 2 varchar 6 titles title_id 1 varchar 6


Reader Feedback

Von S. writes: In your FUNCTION dob.udf_Tbl_PKeyColumnsTAB, I took your where clause

 WHERE tc.CONSTRAINT_TYPE = 'PRIMARY KEY' AND (@Tbl_Name is NULL OR tc.TABLE_NAME = @Tbl_Name) AND tc.TABLE_NAME != 'dtproperties'

and changed it to

 WHERE (tc.CONSTRAINT_TYPE = 'PRIMARY KEY') AND (tc.TABLE_NAME = IsNull(@Tbl_Name,tc.TABLE_NAME)) AND (tc.TABLE_NAME != 'dtproperties')

Just something to think about. I did like your code.

SMaloney. writes: This is a nice introduction to using the information schema views. It is laudable to encourage using INFORMATION_SCHEMA views (instead querying system tables) whenever possible.

However, the statement that "Everything needed is in the INFORMATION_SCHEMA views" may well be misinterpreted by those unfamiliar with INFORMATION_SCHEMA views (the audience for the tip -- therefore the statement bears an important caveat).

Specifically, when the goal is to determine which MS SQL Server columns may possibly be acting as keys for a database or application system, relying on INFORMATION_SCHEMA views is not sufficient. Key columns may or may not be implemented within a DBMS. Initially assuming that database key columns do use built in Sql Server DBMS functionality (to guarantee unique key column values); a logical way to begin is by looking at DRI Key columns (INFORMATION_SCHEMA views may be implemented reasonably well for that task). From there however, one would be wise to examine unique constraints, identity columns, and GUID or uniqueidentifier columns. {Of these, GUID columns in a MS Sql Server 2k database may be easily located using the [INFORMATION_SCHEMA].[COLUMNS] view.}

However, identifying keys implemented by means of Sql Server unique constraints or identity columns is better handled by special (system) stored procedures (such as sp_help or by system table queries, rather than by using INFORMATION_SCHEMA views). For details, see the SSWUG article "Where Did Sql Server Put My Keys?" at http://www.sswug.org/see/13833.


For More Information

  • Feedback: E-mail the editor with your thoughts about this tip.
  • More tips: Hundreds of free SQL Server tips and scripts.
  • Tip contest: Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
  • Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
  • Forums: Ask your technical SQL Server questions--or help out your peers by answering them--in our active forums.
  • Best Web Links: SQL Server tips, tutorials, and scripts from around the Web.

This was first published in May 2003

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.