May 21, 2012

sp_MSmatchkey (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_MSmatchkey(nvarchar @tablename
, nvarchar @col1
, nvarchar @col2
, nvarchar @col3
, nvarchar @col4
, nvarchar @col5
, nvarchar @col6
, nvarchar @col7
, nvarchar @col8
, nvarchar @col9
, nvarchar @col10
, nvarchar @col11
, nvarchar @col12
, nvarchar @col13
, nvarchar @col14
, nvarchar @col15
, nvarchar @col16)

MetaData:

   
create proc sys.sp_MSmatchkey
@tablename nvarchar(517),
@col1 nvarchar(258),
@col2 nvarchar(258) = null,
@col3 nvarchar(258) = null,
@col4 nvarchar(258) = null,
@col5 nvarchar(258) = null,
@col6 nvarchar(258) = null,
@col7 nvarchar(258) = null,
@col8 nvarchar(258) = null,
@col9 nvarchar(258) = null,
@col10 nvarchar(258) = null,
@col11 nvarchar(258) = null,
@col12 nvarchar(258) = null,
@col13 nvarchar(258) = null,
@col14 nvarchar(258) = null,
@col15 nvarchar(258) = null,
@col16 nvarchar(258) = null
as

create table #t1 ( -- Join into this... --
i int NOT NULL,
name nvarchar(258) COLLATE database_default NULL
)

create table #i1 (
i int NOT NULL
)

declare @id int, @ii int, @colnotfound nvarchar(258), @keycnt int
select @id = object_id(@tablename)
if (@id is null) begin
RAISERROR (15001, -1, -1, @tablename)
return 1
end
select @ii = 1
insert #t1 values (1, @col1)
insert #t1 values (2, @col2)
insert #t1 values (3, @col3)
insert #t1 values (4, @col4)
insert #t1 values (5, @col5)
insert #t1 values (6, @col6)
insert #t1 values (7, @col7)
insert #t1 values (8, @col8)
insert #t1 values (9, @col9)
insert #t1 values (10, @col10)
insert #t1 values (11, @col11)
insert #t1 values (12, @col12)
insert #t1 values (13, @col13)
insert #t1 values (14, @col14)
insert #t1 values (15, @col15)
insert #t1 values (16, @col16)
delete #t1 where name is null

select @colnotfound = min(name) from #t1 where name not in (select name from dbo.syscolumns where id = @id)
if (@colnotfound is not null) begin
RAISERROR (14305, -1, -1, @colnotfound, @tablename)
return 1
end
select @ii = 1, @keycnt = count(*) from #t1

-- Load all indexes which have the matching number of columns into a temp table, then eliminate those which don't qualify. --
-- Remember the RID in the nc index is counted as a key --
insert #i1 select indid from dbo.sysindexes where status & 0x1800 <> 0
and id = @id and keycnt - (case indid when 1 then 0 else 1 end) = @keycnt
while (@ii <= @keycnt) begin
delete #i1 from #i1 i, #t1 t where t.i = @ii and index_col(@tablename, i.i, t.i) <> t.name
select @ii = @ii + 1
end

-- The qualifying key will be the lowest indid (or the ONLY indid, if we disallow duplicate indexes), if any remain. --
select name from dbo.sysindexes where id = @id and indid = (select min(i) from #i1)

No comments:

Post a Comment

Total Pageviews