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.

Answer 1

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