Compare single values in SQL to identify candidate keys

Question

I need to check if a column is candidate key. To make it generic, I created a stored procedure:

ALTER PROCEDURE [dbo].[CheckPK] 
(
    @tableName VARCHAR(100),
    @pk VARCHAR(100)
)
AS
PRINT 'Checking for candidate key ' + @pk + ' for table: ' + @tableName

DECLARE @sql NVARCHAR(4000)

SET @sql = 'select count(distinct ([' + @pk + '])) as tot_pk from ' + @tableName + ' select count (*) as tot_real from ' + @tableName

EXEC sp_executesql @sql

Which works fine. What I have to do is verify that the two selected values are the same.

QUESTION: is it possible to add an if(tot_pk = tot_real) type of condition?

I am a newbie in SQL, so I apologise if this question seems trivial. Thank you

So you wanna know if a particular column just have _unique_ values, right? Because you can look into the `sys` schema for primary key and constraint definitions — Rubens Farias, Jan 04 '17 at 15:43
`select count(distinct pk)` will always return the same result as `select count(*)`. This is because pk is unique and non-nullable by definition, meaning that every record in the table will have a unique value as it's primary key. — Zohar Peled, Jan 04 '17 at 15:44
@ZoharPeled I need to verify that pk is actually a primary key. — Nic, Jan 04 '17 at 15:45
I apologise for the duplicate. I'm going to close the question. — Nic, Jan 04 '17 at 15:58
What you are actually trying to do is *determine whether a single column is a "candidate" key*. — RBarryYoung, Jan 04 '17 at 16:36
I changed it in your post (people where down-voting you because you appeared to be misusing the term "primary key"). — RBarryYoung, Jan 04 '17 at 18:01

score 0 · Answer 1 · answered Jan 04 '17 at 16:57

Try this code. Maybe is that you need.

I added an optional parameter @Schema.

CODE

--EXEC [dbo].[CheckPK] 'name of table','name of column', 'schema is optional'

ALTER PROCEDURE [dbo].[CheckPK] 
(
    @tableName VARCHAR(100)
    , @pk VARCHAR(100)
    , @Schema VARCHAR(100) = NULL
)
AS
BEGIN
    BEGIN TRY
        PRINT 'Checking primary key ' + @pk + ' for table: ' + @tableName

        DECLARE @sql NVARCHAR(4000)

        SET @sql = 'select count(distinct ([' + @pk + '])) as tot_pk from '
+ ISNULL(@Schema + '.', '') + @tableName + ' select count (*) as tot_real from ' + ISNULL(@Schema + '.', '') + @tableName

        EXEC sp_executesql @sql

        IF EXISTS (
            SELECT COLUMN_NAME
            FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
            WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + CONSTRAINT_NAME), 'IsPrimaryKey') = 1
                AND TABLE_NAME = @tableName
                AND TABLE_SCHEMA = ISNULL(@Schema, TABLE_SCHEMA)
                AND COLUMN_NAME = @pk
            )
           SELECT @pk + ' IS A PRYMARY KEY'
       ELSE
           SELECT @pk + ' IS NOT A PRYMARY KEY'
   END TRY

   BEGIN CATCH
       SELECT 'An error has occurred. Verify that ''' + @tableName + ''' it is a table and has the correct schema and ''' + @pk + ''' is a valid column of the table'
   END CATCH
END

Thanks @JotaPardo. I found a few missing quote signs as sql must be an executable string. — Nic, Jan 04 '17 at 17:35

Compare single values in SQL to identify candidate keys

1 Answers1