hx_GetPKLessTables

,

Returns a list of table names from the current database

Which do not have primary keys assigned.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[hx_GetPKLessTables]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[hx_GetPKLessTables]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

/*Procedure:	hx_GetPKLessTables
  Author:	David Stocks
  Written:	21August 2001
  Purpose:	Returns a list of table names from the current database
		Which do not have primary keys assigned
  Input:		None
  Output:	Table format
  Warnings:	None
*/

CREATE PROCEDURE hx_GetPKLessTables AS

declare @SQL Varchar(500)
Declare @DBName as Varchar(50)
Set @DBName = db_name()

Set @Sql = 'Select Name From ' + QuoteName(@DBName) + '.[dbo].[SysObjects] Where xtype = ''u'' And Name Not In '
Set @Sql = @SQL + '(Select Distinct o.Name '
Set @Sql = @SQL +  'From ' + QuoteName(@DBName) + '.[dbo].[SysIndexes] i, '  + QuoteName(@DBName) + '.[dbo].[SysColumns]'
Set @Sql = @SQL +  ' c, ' + QuoteName(@DBName) + '.[dbo].[SysObjects] o '
Set @Sql = @SQL +  'Where o.ID = c.ID And o.ID = i.ID And (i.Status & 0x800) = 0x800)'
Exec(@Sql)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Rate

Share

Share

Rate