合并SQL语句结果
0
悬赏园豆：50
[已关闭问题]
已知,下边的语句可以查询库中所有的表名,
SELECT NAME FROM DBO.SYSOBJECTS WHERE OBJECTPROPERTY(ID, N'ISUSERTABLE') = 1 ORDER BY [NAME]
已知,下边的存储过程可查询某表的表键
sp_pkeys _etpass_sms
如何合并两个SQL,以达到这样的查询结果
|表名|该表主键名|
前提,不想使用循环,如果一定没有办法,循环是最后的底线,希望能有其它办法解决,
最后,祝各位新年快乐!
其他回答(3)
0
用下面这个SQL语句就可以了：
select
TableName = convert(sysname,o.name), PK_NAME = convert(sysname,i.name)
from
sys.indexes i,
DBO.SYSOBJECTS o
where
o.id = i.object_id and
i.is_primary_key = 1 and
OBJECTPROPERTY(ID, N'ISUSERTABLE') = 1 ORDER BY TableName
0
Create table #temp
(
a varchar(100),
b varchar(30),
c varchar(30),
d varchar(30),
e int,
f varchar(30)
)
Declare @TableName Varchar(100)
Declare GetKeysByTableName cursor local static
for SELECT NAME FROM DBO.SYSOBJECTS WHERE OBJECTPROPERTY(ID, N'ISUSERTABLE') = 1 ORDER BY [NAME]
open GetKeysByTableName
Fetch GetKeysByTableName into @TableName
while @@FETCH_STATUS = 0
begin
insert into #temp exec sp_pkeys @TableName
Fetch GetKeysByTableName into @TableName
end
CLOSE GetKeysByTableName
DEALLOCATE GetKeysByTableName
select c as tablename,f as keyname from #temp
drop table #temp
0
MARK 1# 2#, give the score to 3# :)
0
hehe....
