SQL SERVER中查询无主键的SQL

--生成表

IF  EXISTS ( SELECT  name
                FROM    sysobjects
                WHERE   xtype = 'u' )
    BEGIN
        DROP TABLE TableRowCount
    END
go
   CREATE TABLE TableRowCount
            (
              ID INT IDENTITY(1, 1) ,
              TableName VARCHAR(500) ,
              ColumnsCount INT DEFAULT ( 0 )
            )
DECLARE @TableName VARCHAR(50) ,
    @sql VARCHAR(MAX)= ''


DECLARE My_Cursor CURSOR
FOR
    ( SELECT    name
      FROM      sysobjects
      WHERE     xtype = 'u'
    )  
OPEN My_Cursor;
FETCH NEXT FROM My_Cursor INTO @TableName;  
WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @sql = 'INSERT INTO TableRowCount  ( TableName, ColumnsCount ) SELECT '''
            + @TableName
            + '''  AS TableName ,COUNT(1) AS ColumnsCount FROM dbo.'
            + @TableName + ';'
        EXEC (@sql)
        FETCH NEXT FROM My_Cursor INTO @TableName;
    END
CLOSE My_Cursor;
DEALLOCATE My_Cursor;
--PRINT @sql

--EXEC (@sql)

SELECT  * FROM    TableRowCount WHERE ColumnsCount > 0 ORDER BY ColumnsCount DESC

 

 

--查主键

 SELECT  b.name AS '表名' ,
            CASE WHEN c.NAME IS NULL THEN '无'
                 ELSE '有'
            END AS '主键'
    FROM    sysobjects b
            LEFT JOIN ( ( SELECT    OBJECT_NAME(a.parent_obj) AS name
                          FROM      sysobjects a
                          WHERE     xtype = 'PK'
                        ) ) c ON b.NAME = c.name
    WHERE   b.xtype = 'U'
    ORDER BY c.NAME DESC

转载于:https://www.cnblogs.com/liugang/p/5725063.html