[SQLCop].[test Tables without a primary key]

ChrisGStevens · November 30, 2017 9:05PM

Need to handle the SQL Version differences.
This is the perfect example. [SQLCop].[test Tables without a primary key]

When reviewing the SQLCop failures I noticed that I had Database Users instead of the schema.
The odd thing was these users don't have anything to do with the tables in question so got me looking.
I updated my test to show schemas instead of the users.

-- SQL Server 2005 or higher
	SELECT s.name + '.' + o.name + CHAR(13) + CHAR(10)
	FROM sys.objects o
		INNER JOIN sys.schemas s
			ON o.schema_id = s.schema_id
		LEFT JOIN
		(SELECT parent_obj FROM sysobjects WHERE xtype = 'PK') AS PrimaryKeys
			ON o.object_id = PrimaryKeys.parent_obj
	WHERE o.type = 'U'
		  AND PrimaryKeys.parent_obj IS NULL
		  AND s.name <> 'tSQLt'
	ORDER BY s.name,
			 o.name;

This is what comes with SQLTest when you install SQLCop.

-- SQL Server 2000 or Lower, users equaled schemas and that is old school.
	SELECT su.name + '.' + AllTables.name
	FROM
	(SELECT name, id, uid FROM sysobjects WHERE xtype = 'U') AS AllTables
		INNER JOIN sysusers su
			ON AllTables.uid = su.uid
		LEFT JOIN
		(SELECT parent_obj FROM sysobjects WHERE xtype = 'PK') AS PrimaryKeys
			ON AllTables.id = PrimaryKeys.parent_obj
	WHERE PrimaryKeys.parent_obj IS NULL
		  AND su.name <> 'tSQLt'
	ORDER BY su.name,
			 AllTables.name;

Eddie D · December 18, 2017 4:22PM

Hi, thank you for your forum post.

First, SQL Test does not support SQL 2000.

What version of SQL Test are you using?

What version of SSMS are you using?

Using SQL Test V2.0.4.295 with SSMS 17.3, the version of [SQLCop].[test Tables without a primary key] is as follows:

/****** Object:  StoredProcedure [SQLCop].[test Tables without a primary key]    Script Date: 18/12/2017 16:15:07 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [SQLCop].[test Tables without a primary key]
AS
BEGIN
	-- Written by George Mastros
	-- February 25, 2012
	-- http://sqlcop.lessthandot.com
	-- http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/best-practice-every-table-should-have-a
	
	SET NOCOUNT ON
	
	DECLARE @Output VarChar(max)
	SET @Output = ''

	SELECT	@Output = @Output + su.name + '.' + AllTables.Name + Char(13) + Char(10)
	FROM	(
			SELECT	Name, id, uid
			From	sysobjects
			WHERE	xtype = 'U'
			) AS AllTables
			INNER JOIN sysusers su
				On AllTables.uid = su.uid
			LEFT JOIN (
				SELECT parent_obj
				From sysobjects
				WHERE  xtype = 'PK'
				) AS PrimaryKeys
				ON AllTables.id = PrimaryKeys.parent_obj
	WHERE	PrimaryKeys.parent_obj Is Null
			AND su.name <> 'tSQLt'
	ORDER BY su.name,AllTables.Name

	If @Output > '' 
		Begin
			Set @Output = Char(13) + Char(10) 
						  + 'For more information:  '
						  + 'http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/best-practice-every-table-should-have-a' 
						  + Char(13) + Char(10) 
						  + Char(13) + Char(10) 
						  + @Output
			EXEC tSQLt.Fail @Output
		End	
END;
GO

Many Thanks
Eddie

buinauskas · February 23, 2018 8:29AM

I've modified my test and have test case as follows:

    SELECT @Output += s.name + '.' + t.name + CHAR(13) + CHAR(10)
    FROM sys.tables AS t
    INNER JOIN sys.schemas AS s
        ON s.schema_id = t.schema_id
    WHERE t.is_ms_shipped = 0
    AND NOT EXISTS (   SELECT *
                       FROM sys.indexes AS i
                       WHERE i.object_id = t.object_id
                       AND i.is_primary_key = 1)
    ORDER BY s.name, t.name;

Works like a charm.

[SQLCop].[test Tables without a primary key]

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk