June 11, 2012

sp_table_type_pkeys (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_table_type_pkeys(nvarchar @table_name
, nvarchar @table_owner
, nvarchar @table_qualifier)

MetaData:

   
create procedure sys.sp_table_type_pkeys
(
@table_name sysname,
@table_owner sysname = null,
@table_qualifier sysname = null
)
as
if @table_qualifier is not null
begin
if db_name() <> @table_qualifier
begin -- If qualifier doesn't match current database
raiserror (15250, -1,-1)
return
end
end

select
TABLE_QUALIFIER = s_pkv.TABLE_CATALOG,
TABLE_OWNER = s_pkv.TABLE_SCHEMA,
TABLE_NAME = s_pkv.TABLE_NAME,
COLUMN_NAME = s_pkv.COLUMN_NAME,
KEY_SEQ = s_pkv.ORDINAL,
PK_NAME = s_pkv.PK_NAME
from
sys.spt_table_type_primary_keys_view s_pkv
where
(@table_name is null or s_pkv.TABLE_NAME = @table_name) and
(@table_owner is null or schema_id(@table_owner) = s_pkv.schema_id)
order by 1, 2, 3, 5

No comments:

Post a Comment

Total Pageviews