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

Question

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?

I'll be that if you eliminate the join to the `sys.index_columns` table you will only get one row per index. This multiplication is occurring due to the indexes having N columns included and therefore repeats N times in the resultset. — gvee, Feb 13 '14 at 16:05

score 1 · Accepted Answer · edited Jul 29 '20 at 18:43

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)

Hey,,thanx for the inpute but I need all columns of a table not only primary key and I dont want the duplicates..Is there any way to get that result. — user3306533, Feb 13 '14 at 18:09
This gives you all the columns, and removes the duplicates. [See this example of a simple table](http://sqlfiddle.com/#!3/1267d/2). Have you actually tried running the query I posted? — GarethD, Feb 13 '14 at 18:18
This request is fantastic! Though, there is one small problem when we have columns participating in multiple foreign key constraints. Let's say we have table `prob`, with columns `building_id`, `floor_id` and `room_id`. And say that a building (in the eponym table) is identified by `building_id` alone, but a floor (in the eponym table) is identified by `building_id` + `floor_id`, and a room (in the eponym table) is identified by `building_id` + `floor_id` + `room_id`. Then the column `prob.building_id` will be displayed 3 times in the above request, and `floor_id` twice. Is that fixable? — user3341592, Nov 09 '17 at 14:58

Yumei De Armas · Answer 2 · 2014-02-13T16:27:34.917

0

What happens if you use the "DISTINCT"? so

SELECT distinct c.NAME...

edited Feb 13 '14 at 16:27

answered Feb 13 '14 at 16:19

Yumei De Armas

386
2
6

2

Better fix the root cause, not slap a bandaid over the bug. – usr Feb 13 '14 at 16:28
Yep, I was also looking at where the issue is, but I haven't found it yet. It looks like if he removed the left joins with the index_columns, then it would be fine, but he will not know if it's primary key or not. I guess it has to be some other way to do it, but I am not that familiar with these tables – Yumei De Armas Feb 13 '14 at 16:34

score 0 · Answer 3 · edited May 23 '17 at 11:49

0

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

If you need to get foreign key columns could you not use this SO post . And remove the joins to sys.index_column and sys.indexes?

edited May 23 '17 at 11:49

Community

1
1

answered Feb 13 '14 at 16:38

twrowsell

467
3
8

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

3 Answers3