By submitting my Email address I confirm that I have read and accepted the Terms of Use and Declaration of Consent.

By submitting your personal information, you agree that TechTarget and its partners may contact you regarding relevant content, products and special offers.

You also agree that your personal information may be transferred and processed in the United States, and that you have read and agree to the Terms of Use and the Privacy Policy.

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:

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 --------------------/

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.

0 comments

Register

Login

Forgot your password?

Your password has been sent to:

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