Query to generate PKs

[guilhermebma]

Hi,

I made a query to generate Pks of tables. The code is below:

SELECT DISTINCT 
         KCU.TABLE_SCHEMA
        ,KCU.TABLE_CATALOG
        ,KCU.TABLE_NAME
        ,'ALTER TABLE '+KCU.TABLE_SCHEMA + '.' + KCU.TABLE_NAME 
                +' CONSTRAINT '+  KCU.CONSTRAINT_NAME 
                +' PRIMARY KEY ("'+lower(KCU.COLUMN_NAME)+'");' as GENERATE_PK
FROM  INFORMATION_SCHEMA.TABLES T
    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE as KCU ON
            T.TABLE_SCHEMA      = KCU.TABLE_SCHEMA
        AND T.TABLE_NAME        = KCU.TABLE_NAME
        AND T.TABLE_CATALOG     = KCU.TABLE_CATALOG
        AND OBJECTPROPERTY(OBJECT_ID(KCU.CONSTRAINT_SCHEMA + '.' + QUOTENAME(KCU.CONSTRAINT_NAME)), 'IsPrimaryKey') = 1
WHERE    T.TABLE_CATALOG        = '%information_schema.database_name%'
     AND T.TABLE_TYPE           = 'BASE TABLE'
     AND T.TABLE_NAME NOT IN ('dtproperties')
ORDER BY 1,2,3;

I hope this code help you to increase your solution.

Tks.

`

[isapir]

I am not using it yet to create the PK, but I added it to the Information Schema query following your suggestion at 33b0290

Thank you.

[isapir]

I actually had to comment that addition out because the Group By didn't work properly and the query returned duplicate rows

[cinava]

It's a great tool, however in my case all tables were generated without primary keys.
I'm using SQL Server 2012 and PostgreSQL 12.
Is there any way to correctly generated primary keys and foreign keys?