|
|
SQL Server T-SQL User-Defined Function of the Week
Showing the Columns of Primary Key
Volume 1 Number 8 January 6, 2003
Sign up for this newsletter at:
http://www.novicksoftware.com/UdfOfWeek/UDFofWeekSignup.htm
I recently answered a posting asking for 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.
This issue's UDF is udf_Tbl_PKeyColumnsTAB. It returns the
columns in the primary key along with data type information.
Here's the CREATE FUNCTION script:
/------------- Copy From 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, 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 that you want primary key
information for 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 the function
udf_SQL_Tbl_NotOwnedbyDBLTBL from this newsletter issue Volume 1
Number 1 at:
http://www.novicksoftware.com/UdfOfWeek/UDFofWeekArchive.htm
for a function 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 From 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 --------------------/
(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 --------------------/
(Results)
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
Happy New Year!
+--------------------------------------------------------------+
Do you have a great UDF that you'd like to share? Or maybe you
have a T-SQL problem that you think could be solved by a UDF
but you don't know how? Send them to:
UDF@NovickSoftware.com
and they might be published in this newsletter. I try and
respond to every request that I get.
Thanks,
Andrew Novick
+--------------------------------------------------------------+
|
|
