- create table Test
- (
- T_RecordID uniqueidentifier not null primary key,
- T_UserID nvarchar(10),
- )
- create table TestLine
- (
- TL_RecordID uniqueidentifier not null primary key,
- TL_T_RecordID uniqueidentifier not null references Test(T_RecordID),
- )
- --primary key(参考sp_pkeys):
- --exec sp_pkeys @table_name='TestLine'
- declare @tablename as nvarchar(30)
- set @tablename='TestLine'
- select column_name = convert(sysname,c.name)
- from sysobjects o
- join sysindexes i on i.id=o.id
- join syscolumns c on c.id=o.id
- where o.id = object_id(@tablename)
- and (i.status & 0x800) = 0x800
- and (c.name = index_col (@tablename, i.indid, 1) or
- c.name = index_col (@tablename, i.indid, 2) or
- c.name = index_col (@tablename, i.indid, 3) or
- c.name = index_col (@tablename, i.indid, 4) or
- c.name = index_col (@tablename, i.indid, 5) or
- c.name = index_col (@tablename, i.indid, 6) or
- c.name = index_col (@tablename, i.indid, 7) or
- c.name = index_col (@tablename, i.indid, 8) or
- c.name = index_col (@tablename, i.indid, 9) or
- c.name = index_col (@tablename, i.indid, 10) or
- c.name = index_col (@tablename, i.indid, 11) or
- c.name = index_col (@tablename, i.indid, 12) or
- c.name = index_col (@tablename, i.indid, 13) or
- c.name = index_col (@tablename, i.indid, 14) or
- c.name = index_col (@tablename, i.indid, 15) or
- c.name = index_col (@tablename, i.indid, 16)
- )
- --foreign key:
- select t1.name,t2.rtableName,t2.name
- from
- (
- select col.name, f.constid as temp
- from syscolumns col,sysforeignkeys f
- where f.fkeyid=col.id
- and f.fkey=col.colid
- and f.constid in
- (
- select distinct(id)
- from sysobjects
- where object_name(parent_obj)=@tablename
- and xtype='F'
- )
- ) as t1
- join
- (
- select object_name(f.rkeyid) as rtableName,col.name,f.constid as temp
- from syscolumns col,sysforeignkeys f
- where f.rkeyid=col.id
- and f.rkey=col.colid
- and f.constid in
- (
- select distinct(id)
- from sysobjects
- where object_name(parent_obj)=@tablename
- and xtype='F'
- )
- ) as t2 on t2.temp=t1.temp
转载于:https://blog.51cto.com/lovgef/1186801