Query to return tables which have no default value or binding for primary key in SQL Server

Question

I am working on different databases. I need to write a query that will return tables name which have a primary key with data type uniqueidentifier and which have no default value for Default Value or Binding

Can anyone please help me? Thanks in advance.

[OFF-TOPIC]Why have you created default constraint on a primary key column? I don't see any use of it. At max the default value can be inserted only once — Pரதீப், Feb 02 '17 at 05:24
I have not created any default constraint. It is old database that is created by other person but I have to work on it. — Pedram, Feb 02 '17 at 05:25
Have a look [here](http://stackoverflow.com/questions/3930338/sql-server-get-table-primary-key-using-sql-query) and [here](http://stackoverflow.com/questions/95967/how-do-you-list-the-primary-key-of-a-sql-server-table). — H.G. Sandhagen, Feb 02 '17 at 05:34

score 0 · Accepted Answer · answered Feb 02 '17 at 07:47

I prefer to make use of INFORMATION_SCHEMA:

SELECT  C.TABLE_CATALOG,
        C.TABLE_SCHEMA,
        C.TABLE_NAME,
        C.COLUMN_NAME,
        C.DATA_TYPE,
        C.COLUMN_DEFAULT
FROM    INFORMATION_SCHEMA.COLUMNS C
        INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE U 
            ON  C.TABLE_CATALOG = U.TABLE_CATALOG
                AND C.TABLE_SCHEMA = U.TABLE_SCHEMA
                AND C.TABLE_NAME = U.TABLE_NAME
                AND C.COLUMN_NAME = U.COLUMN_NAME
WHERE   OBJECTPROPERTY(OBJECT_ID(U.CONSTRAINT_SCHEMA + '.' + QUOTENAME(U.CONSTRAINT_NAME)), 'IsPrimaryKey') = 1
        AND C.DATA_TYPE = 'uniqueidentifier'
        AND C.COLUMN_DEFAULT IS NULL

Query to return tables which have no default value or binding for primary key in SQL Server

1 Answers1