Column Table Information In Sql Server
Submitted by manjunathcbhat on Thu, 06/19/2014 - 07:21
There are numerous occasions on daily bases where we need some basic information of the Tables and Columns used in a Database for many different purpose.
Here is a small script which gives you some basic information of Tables and Columns. There are many more columns in the catalogs which I have used, which you can modify, add, Remove, Customise as per your needs.
SELECT DISTINCT SS.NAME AS [TABLE_SCHEMA],ST.NAME AS TABLE_NAME, SC.[NAME] AS COLUMN_NAME ,SC.COLLATION_NAME,SC.IS_NULLABLE AS COLUMN_NULLABLE,SC.IS_REPLICATED AS COLUMN_REPLICATED ,CAST(STY.NAME AS VARCHAR(20)) +'('+ CAST(CASE ST.NAME WHEN 'NVARCHAR' THEN (SELECT SC.MAX_LENGTH/2) ELSE (SELECT SC.MAX_LENGTH) END AS VARCHAR(20)) +')' AS [DATA_TYPE], SC.IS_IDENTITY,ST.IS_PUBLISHED AS TABLE_PUBLISHED,ST.IS_REPLICATED AS TABLE_REPLICATED FROM SYS.COLUMNS SC INNER JOIN SYS.TABLES ST ON SC.OBJECT_ID = ST.OBJECT_ID INNER JOIN INFORMATION_SCHEMA.COLUMNS ISC ON SC.NAME = ISC.COLUMN_NAME INNER JOIN SYS.TYPES AS STY ON SC.USER_TYPE_ID = STY.USER_TYPE_ID INNER JOIN SYS.SCHEMAS AS SS ON SS.SCHEMA_ID = ST.SCHEMA_ID