SQL Server Mentalist

Hi Folks,

In this article we are going to cover How to Create Primary Key for all Tables.

In this post we are going to discuss following points:

• Problem Statement of SQL Script:
• Description of SQL Script:
• SQL Script Code
• User Level to execute

Problem Statement of SQL Script:

How to Create Primary Key for all Tables?

Description of SQL Script:

This script will Create Primary Key for all Tables .

SQL Script Code

DECLARE @object_id INT;
DECLARE @parent_object_id INT;
DECLARE @TSQL NVARCHAR(4000);
DECLARE @COLUMN_NAME SYSNAME;
DECLARE @is_descending_key BIT;
DECLARE @col1 BIT;
DECLARE @action CHAR(6);

--SET @action = 'DROP';
SET @action = 'CREATE';

DECLARE PKcursor CURSOR
FOR
SELECT kc.object_id
    ,kc.parent_object_id
FROM sys.key_constraints kc
INNER JOIN sys.objects o ON kc.parent_object_id = o.object_id
WHERE kc.type = 'PK'
    AND o.type = 'U'
    AND o.NAME NOT IN (
        'dtproperties'
        ,'sysdiagrams'
        ) -- not true user tables
ORDER BY QUOTENAME(OBJECT_SCHEMA_NAME(kc.parent_object_id))
    ,QUOTENAME(OBJECT_NAME(kc.parent_object_id));

OPEN PKcursor;

FETCH NEXT
FROM PKcursor
INTO @object_id
    ,@parent_object_id;

WHILE @@FETCH_STATUS = 0
BEGIN
    IF @action = 'DROP'
        SET @TSQL = 'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME
                (@parent_object_id)) + '.' + QUOTENAME(
                OBJECT_NAME(@parent_object_id)) + 
            ' DROP CONSTRAINT ' + QUOTENAME(OBJECT_NAME(
                    @object_id))
    ELSE
    BEGIN
        SET @TSQL = 'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME
                (@parent_object_id)) + '.' + QUOTENAME(
                OBJECT_NAME(@parent_object_id)) + 
            ' ADD CONSTRAINT ' + QUOTENAME(OBJECT_NAME(
                    @object_id)) + ' PRIMARY KEY' + CASE 
                INDEXPROPERTY(@parent_object_id, OBJECT_NAME(
                        @object_id), 'IsClustered')
                WHEN 1
                    THEN ' CLUSTERED'
                ELSE ' NONCLUSTERED'
                END + ' (';

        DECLARE ColumnCursor CURSOR
        FOR
        SELECT COL_NAME(@parent_object_id, ic.column_id)
            ,ic.is_descending_key
        FROM sys.indexes i
        INNER JOIN sys.index_columns ic ON i.object_id = ic.
            object_id
            AND i.index_id = ic.index_id
        WHERE i.object_id = @parent_object_id
            AND i.NAME = OBJECT_NAME(@object_id)
        ORDER BY ic.key_ordinal;

        OPEN ColumnCursor;

        SET @col1 = 1;

        FETCH NEXT
        FROM ColumnCursor
        INTO @COLUMN_NAME
            ,@is_descending_key;

        WHILE @@FETCH_STATUS = 0
        BEGIN
            IF (@col1 = 1)
                SET @col1 = 0
            ELSE
                SET @TSQL = @TSQL + ',';

            SET @TSQL = @TSQL + QUOTENAME(@COLUMN_NAME) + ' ' + CASE 
                    @is_descending_key
                    WHEN 0
                        THEN 'ASC'
                    ELSE 'DESC'
                    END;

            FETCH NEXT
            FROM ColumnCursor
            INTO @COLUMN_NAME
                ,@is_descending_key;
        END;

        CLOSE ColumnCursor;

        DEALLOCATE ColumnCursor;

        SET @TSQL = @TSQL + ');';
    END;

    PRINT @TSQL;

    FETCH NEXT
    FROM PKcursor
    INTO @object_id
        ,@parent_object_id;
END;

CLOSE PKcursor;

DEALLOCATE PKcursor;

User Level to execute

400

Hope you will like How to Create Primary Key for all Tables.

If you really like reading my blog and understood at least few thing then please don’t forget to subscribe my blog.

If you want daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog :

Link Resource Website

For More information related to BI World visit my Mentalist Blog

SQL Server Mentalist >> SQL Learning Blog

Business Intelligence Mentalist >> BI World

Infographic Mentalist >> Image worth explaining thousand Words

Microsoft Mentalist >> MVC,ASP.NET, WCF & LinQ

DBA Mentalist >>Advance SQL Server Blog

Microsoft BI Mentalist >> MS BI Development Update

Connect With me on

| FaceBook |Twitter | linkedIn| Google+ | WordPress | RSS |