Reputation: 3

Get list of all columns with complete details (Identity, nullabel,primary key) in sql server without duplicate column

I have need to automate the process of database upgrade process. So by getting the column structure of a table i need to create update/insert/create sql statement's .

BY problem was i am getting duplicate column name as constrains are also coming in the list which i don't need. So is there a way to restrict constraints in the result set.

I am using below query

SELECT c.NAME 'Column Name'
,t.NAME 'Data type'
,c.max_length 'Max Length'
,c.precision
,c.scale
,c.is_nullable AS 'Is Nullable'
,c.is_identity AS 'Is Identity'
,ISNULL(i.is_primary_key, 0) 'Primary Key'
,i.type_desc
FROM fblfeb12.sys.columns c
INNER JOIN fblfeb12.sys.types t ON c.system_type_id = t.system_type_id
LEFT OUTER JOIN fblfeb12.sys.index_columns ic ON ic.object_id = c.object_id
AND ic.column_id = c.column_id
LEFT OUTER JOIN fblfeb12.sys.indexes i ON ic.object_id = i.object_id
AND ic.index_id = i.index_id
WHERE c.object_id = OBJECT_ID('table name')

Result set:

 Pms_ID             uniqueidentifier    16  0   0   0   0   CLUSTERED
 Pms_PRODMODELID    uniqueidentifie  16 0   0   1   0NONCLUSTERED
 Pms_PRODMODELID    uniqueidentifier    16  0   0   10NONCLUSTERED
 Pms_PRODMODELID    uniqueidentifier    16  0   0   10NONCLUSTERED
 Pms_ATTRIBUTEID    uniqueidentifier    16  0   0   10NONCLUSTERED
 Pms_ATTRIBUTEID    uniqueidentifier    16  0   0   0NONCLUSTERED
 Pms_ATTRIBUTEID    uniqueidentifier    16  0   0   1NONCLUSTERED

where PRODMODELID , ATTRIBUTEID comes 3 times.

I need only foreign key column but here I am getting index,Constraints which i don't need.

I need column name, data-type,identity,primary key, null able, foreign key.

Can you provide me any better solution,If i am doing anything wrong?

Upvotes: 0

Answers (3)

GarethD

Reputation: 69769

The problem is that you are including all indexes on the column. Consider this simple table:

CREATE TABLE #T (ID INT NOT NULL CONSTRAINT PK_T_ID PRIMARY KEY);
CREATE INDEX IX_T_ID ON #T (ID);

When you run an adaption of you query:

SELECT c.name, i.name, i.is_primary_key
FROM tempdb.sys.columns c
    LEFT OUTER JOIN tempdb.sys.index_columns ic 
        ON ic.object_id = c.object_id
        AND ic.column_id = c.column_id
    LEFT OUTER JOIN tempdb.sys.indexes i 
        ON ic.object_id = i.object_id
        AND ic.index_id = i.index_id
WHERE c.object_id = OBJECT_ID('tempdb..#T');

The column name will be duplicated for each index that includes that column (either key or non key), so the result will be:

name    name    is_primary_key
ID      PK_T_ID     1
ID      IX_T_ID     0

Since you only care about the primary key, you can apply the a filter in the join to sys.indexes to only return the primary keys, to do this effectively though you need to make the join between index_columns and indexes an INNER JOIN, but maintain the OUTER JOIN from columns to index_columns which involves slightly rearranging the joins, so the above would become:

SELECT c.name, i.name, i.is_primary_key
FROM tempdb.sys.columns c
    LEFT OUTER JOIN (tempdb.sys.index_columns ic 
        INNER JOIN tempdb.sys.indexes i 
            ON ic.object_id = i.object_id
            AND ic.index_id = i.index_id
            AND i.is_primary_key = 1) -- ONLY PRIMARY KEYS
        ON ic.object_id = c.object_id
        AND ic.column_id = c.column_id
WHERE c.object_id = OBJECT_ID('tempdb..#T');

This removes the duplicate result. Finally you can query sys.foreign_key_columns to find out if the column references another table giving a final query of:

SELECT  c.NAME AS [Column Name]
        ,t.NAME AS [Data type]
        ,c.max_length AS [Max Length]
        ,c.precision
        ,c.scale
        ,c.is_nullable AS [Is Nullable]
        ,c.is_identity AS [Is Identity]
        ,ISNULL(i.is_primary_key, 0) [Primary Key]
        ,i.type_desc
        ,OBJECT_SCHEMA_NAME(fk.object_id) + '.' + OBJECT_NAME(fk.object_id) + ' (' + fk.Name + ')' AS [Foreign Key]
FROM    sys.columns c
        INNER JOIN sys.types t 
            ON c.system_type_id = t.system_type_id
        LEFT OUTER JOIN (sys.index_columns ic 
            INNER JOIN sys.indexes i 
                ON ic.object_id = i.object_id
                AND i.is_primary_key = 1
                AND ic.index_id = i.index_id)
            ON ic.object_id = c.object_id
            AND ic.column_id = c.column_id
        LEFT JOIN sys.foreign_key_columns fkc
            ON fkc.parent_object_id = c.object_id
            AND fkc.parent_column_id = c.column_id
        LEFT JOIN sys.columns fk
            ON fk.object_id = fkc.referenced_object_id
            AND fk.column_id = fkc.referenced_column_id
WHERE c.object_id = OBJECT_ID('table')
ORDER BY c.Column_ID;

N.B I have changed your column aliases from single quotes to brackets as using single quotes is deprecated (not to mention easily confused with string literals)

Upvotes: 1