找出没有主键的表

你知道你的SQL Server哪些表没有主键吗？跑一下这段脚本试试看看...

USE tempdb
GO

IF OBJECT_ID('tempdb..#db', 'U') <> 0 
DROP TABLE #db 

IF OBJECT_ID('tempdb..#dbtable', 'U') <> 0 
DROP TABLE #dbtable 

CREATE TABLE #dbtable(id int identity(1,1), [database_name] varchar(100), table_name varchar(200)) 
SELECT name, px = ROW_NUMBER() OVER(ORDER BY(SELECT 1)) INTO #db FROM sys.databases WITH(NOLOCK) WHERE state = 0 AND database_id > 4 ORDER BY name 

DECLARE @px int 
DECLARE @dbname sysname 
DECLARE @sql varchar(max) 

SELECT @dbname = name, @px = px FROM #db WHERE px = 1; 
WHILE @@rowcount > 0 
BEGIN 
    SET @sql = 'USE ' +QUOTENAME(@dbname)+ CHAR(10) 
    SET @sql = @sql + 'INSERT INTO #dbtable(database_name,table_name) SELECT '''+@dbname+''',name FROM sys.tables WHERE object_id NOT IN(SELECT tab.object_id FROM sys.tables tab INNER JOIN sys.indexes ind ON ind.object_id=tab.object_id WHERE ind.is_primary_key=1) ORDER BY name' 
    exec(@sql) 
    SELECT TOP(1) @dbname = name, @px = px FROM #db WHERE px > @px; 
END 

SELECT * FROM #dbtable

执行结果大概如下：