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

ChrisGStevensChrisGStevens Posts: 10 Bronze 1
edited November 30, 2017 9:08PM in SQL Test
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;

Tagged:

Comments

  • Eddie DEddie D Posts: 1,800 Rose Gold 5
    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
    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
  • 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.
Sign In or Register to comment.