Table No Primary Key Findings

SELECT DB_name() as DBName,SCHEMA_NAME(schema_id) AS SchemaName

,[Name]

FROM [sys].[objects]

WHERE [Type_Desc] = 'USER_TABLE' AND

      OBJECTPROPERTY([Object_ID], 'TableHasPrimaryKey') = 0

ORDER BY [Name]


Row count for Table which does not have primary key:

--Create Procedure P_PrimaryKeyNotExist

--as

--Begin

IF OBJECT_ID('tempdb..#a') IS NOT NULL DROP TABLE #a;

IF OBJECT_ID('tempdb..#b') IS NOT NULL DROP TABLE #b;

SELECT QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TableName],

SCHEMA_NAME(sOBJ.schema_id) AS SchemaName,

sOBJ.name AS Name

, SUM(sPTN.Rows) AS [RowCount] into #b FROM

sys.objects AS sOBJ

 INNER JOIN sys.partitions AS sPTN

 ON sOBJ.object_id = sPTN.object_id WHERE

 sOBJ.type = 'U'AND sOBJ.is_ms_shipped = 0x0

 AND index_id < 2 -- 0:Heap, 1:Clustered

 GROUP BY sOBJ.schema_id, sOBJ.name

 ORDER BY [TableName];

 SELECT DB_name() as DBname,SCHEMA_NAME(schema_id) AS SchemaName,[Name] into #a

FROM [sys].[objects]

WHERE [Type_Desc] = 'USER_TABLE' AND

      OBJECTPROPERTY([Object_ID], 'TableHasPrimaryKey') = 0

      ORDER BY [Name];

select a.DBname,b.[TableName] as FullTableName,a.SchemaName,a.name,b.[RowCount]

from #a a inner join #b b on a.name =b.Name and a.SchemaName = b.SchemaName Order by b.[RowCount] desc;

--End