Question

MSSQL查询所有没有主键的表

Answer 1

说一下大概语句，具体你自己该，假设10个数据库的名称是db1,db2,...,db10，这里可以根据你数据库名称改，下面的语句也要改。
创建一个表存放数据库名，表名
create table tb1
(
id [int] IDENTITY (1, 1),
dbname varchar(100),
tbname varchar(100)
)

--- 把数据库db1的所有表名插入tb1表
insert tb1 (dbname,tbname)
select 'db1',name from db1.dbo,sysobjects where xtype='u'

insert tb1 (dbname,tbname)
select 'db2',name from db2.dbo,sysobjects where xtype='u'

insert tb1 (dbname,tbname)
select 'db3',name from db3.dbo,sysobjects where xtype='u'

insert tb1 (dbname,tbname)
select 'db4',name from db4.dbo,sysobjects where xtype='u'

insert tb1 (dbname,tbname)
select 'db5',name from db5.dbo,sysobjects where xtype='u'

insert tb1 (dbname,tbname)
select 'db6',name from db6.dbo,sysobjects where xtype='u'

insert tb1 (dbname,tbname)
select 'db7',name from db7.dbo,sysobjects where xtype='u'

insert tb1 (dbname,tbname)
select 'db8',name from db8.dbo,sysobjects where xtype='u'

insert tb1 (dbname,tbname)
select 'db9',name from db9.dbo,sysobjects where xtype='u'

insert tb1 (dbname,tbname)
select 'db10',name from db10.dbo,sysobjects where xtype='u'

这样tb1表里面就有数据库名，列名。创建一个存储过程，循环搜索：
create PROCEDURE TESTPERCUDURE
as
declare @col integer
declare @colmax integer
declare @tablename varchar(100)
declare @sql as VARCHAR(200)
BEGIN
set @col=1
set @colmax = (select max(id) from tb1)
set @tablename= ''
while @col<=@colmax
begin
set @tablename= (select dbname+'.dbo.'+tbname from tb1 where id = @col)
set @sql='select * FROM '+@tablename+' where xname = 'aaa' --通过循环获取你的表名和数据库名,之后组装成sql语句,然后执行.
EXECUTE sp_executesql @sql
SET @col=@col+1
end
end

Answer 2

--查询所有没有主键的表语句如下：
SELECT name FROM sys.tables
EXCEPT
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'