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
SQL Server中获取表的主键和外键
最新推荐文章于 2024-03-27 11:58:11 发布