Wednesday 16 July 2014

How to get list of table columns information in Sql Server



SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='TableName'

The above query will gives except primary key information.



Below query included primary key information also



SELECT COLS.COLUMN_NAME, COLS.DATA_TYPE, COLS.CHARACTER_MAXIMUM_LENGTH,
              (SELECT 'PRIMARY KEY' FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU
                              ON COLS.TABLE_NAME = TC.TABLE_NAME
                             AND TC.CONSTRAINT_TYPE = 'PRIMARY KEY'
                             AND KCU.TABLE_NAME = TC.TABLE_NAME
                             AND KCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
                             AND KCU.COLUMN_NAME = COLS.COLUMN_NAME) AS PRIMARY_KEY
        FROM INFORMATION_SCHEMA.COLUMNS COLS WHERE TABLE_NAME = 'TableName' ORDER BY PRIMARY_KEY DESC, COLUMN_NAME

No comments:

Post a Comment