Retreive and update meta property of a table in MSSQL

Introduction

Nowadays many development teams also want to keep their data dictionary further up-to-date but they don't want to waste too much time to update the existing data dictionary. So a great idea is that we can design a web based application for data dictionary purposes. 

We have to provide a function for querying the meta data of each table in our database and a function for adding some comment in each field of each table in our database.

Using the code

Firstly, we must know the databases in SQL Server. To carry out this, we use the following SQL statement:

SELECT name FROM sys.sysdatabases WHERE name not in ('master','tempdb','model','msdb')

And then we can use the following statement to locate our prefered database. And then we need to find out all tables of our selected database, so we type the following SQL statement.

Use [database Name] 
SELECT * FROM  INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'

The query result shows us all table names of our selected database. The next step is we can choose one table to query its meta data. Simply use the following SQL statement to retrieve all meta data of a table which you selected.

SELECT  a.Table_schema +'.'+a.Table_name       as [Table Name]   
   ,b.COLUMN_NAME                          as [(Name)] 
   ,b.IS_NULLABLE                          as [Allow Nulls]  		
   ,b.DATA_TYPE                            as [Data Type]   
   ,isnull(b.COLUMN_DEFAULT,'')            as [Default Value]   
   ,isnull(b.CHARACTER_MAXIMUM_LENGTH,'')  as [Length]   
   ,b.COLLATION_NAME as [Collation] 		        	 
   ,c.CONSTRAINT_TYPE as [Constraint Type]
   ,c.CONSTRAINT_NAME as [Constraint Name]
   ,( SELECT value   
            FROM fn_listextendedproperty (NULL, 'schema', 
            a.TABLE_SCHEMA, 'table', a.TABLE_NAME, 'column', default)   
           WHERE name='MS_Description'    
             and objtype='COLUMN'    
             and objname Collate Chinese_Taiwan_Stroke_CI_AS = b.COLUMN_NAME   
    ) AS [Description] 
   ,CASE WHEN COLUMNPROPERTY(OBJECT_ID(b.TABLE_NAME)
   ,b.COLUMN_NAME,'IsDeterministic') = 1 THEN 'Yes' ELSE 'No' END AS [Deterministic]  
   ,CASE WHEN COLUMNPROPERTY(OBJECT_ID(b.TABLE_NAME),
   b.COLUMN_NAME,'IsFulltextIndexed') = 1 THEN 'Yes' 
   ELSE 'No' END AS [Is Full-text Indexed]   
   ,COLUMNPROPERTY(OBJECT_ID(b.TABLE_NAME),
   b.COLUMN_NAME,'FullTextTypeColumn') AS [Full-text Type Column]   
   ,CASE WHEN COLUMNPROPERTY(OBJECT_ID(b.TABLE_NAME),
   b.COLUMN_NAME,'IsIdentity') = 1 THEN 'Yes' ELSE 'No' END AS [(Is Identity)]		 
   ,b.Increment AS [Identity Increment]
   ,b.Seed AS [Identity Seed]
   ,b.Current_Identity
	,CASE WHEN COLUMNPROPERTY(OBJECT_ID(b.TABLE_NAME),
	b.COLUMN_NAME,'IsIndexable') = 1 THEN 'Yes' ELSE 'No' END AS [Indexable]   		
	,CASE WHEN COLUMNPROPERTY(OBJECT_ID(b.TABLE_NAME)
	,b.COLUMN_NAME,'IsColumnSet') = 1 THEN 'Yes' ELSE 'No' END AS [Is ColumnSet]  
	,CASE WHEN COLUMNPROPERTY(OBJECT_ID(b.TABLE_NAME)
	,b.COLUMN_NAME,'IsSparse') = 1 THEN 'Yes' ELSE 'No' END AS [Is Sparse] 
	,CASE WHEN COLUMNPROPERTY(OBJECT_ID(b.TABLE_NAME)
	,b.COLUMN_NAME,'IsIdNotForRepl') = 1 THEN 'Yes' ELSE 'No' END AS [Not For Replication]  
	,CASE WHEN COLUMNPROPERTY(OBJECT_ID(b.TABLE_NAME)
	,b.COLUMN_NAME,'IsRowGuidCol') = 1 THEN 'Yes' ELSE 'No' END AS [RowGuid]                    
 FROM    
     INFORMATION_SCHEMA.TABLES a   
     LEFT JOIN (SELECT SCH.*,Sed.Seed, Sed.Increment,Sed.Current_Identity FROM
	 INFORMATION_SCHEMA.COLUMNS Sch LEFT OUTER JOIN (
	  SELECT TABLE_NAME
	        ,COLUMN_NAME          
            ,IDENT_SEED(table_name) AS Seed
			,IDENT_INCR(table_name) AS Increment
			,IDENT_CURRENT(TABLE_NAME) AS Current_Identity
			FROM INFORMATION_SCHEMA.COLUMNS 
			WHERE COLUMNPROPERTY(OBJECT_ID(table_name),column_name,'IsIdentity') = 1
     ) Sed ON Sch.TABLE_NAME = Sed.TABLE_NAME and Sch.COLUMN_NAME = Sed.COLUMN_NAME
	 ) b    
     ON a.TABLE_NAME = b.TABLE_NAME 
	 LEFT JOIN (
	SELECT Col.COLUMN_Name, Col.TABLE_NAME, col.CONSTRAINT_NAME,con.CONSTRAINT_TYPE  FROM
		INFORMATION_SCHEMA.TABLE_CONSTRAINTS Con,
		INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE	Col
	WHERE	
 		Col.TABLE_SCHEMA	= Con.TABLE_SCHEMA
	AND	Col.TABLE_NAME		= Con.TABLE_NAME
	AND	Col.CONSTRAINT_NAME	= Con.CONSTRAINT_NAME ) c ON 
	    b.TABLE_NAME = c.TABLE_NAME and b.COLUMN_NAME = c.COLUMN_NAME
 WHERE a.TABLE_TYPE='BASE TABLE' and a.TABLE_NAME = 'You Selected Table Name' 
ORDER BY a.TABLE_NAME      

You may want to add some comment in each table field. For MS SQL, each table field has a built-in property called 'Description' so we can use the following SQL statement to add comments in it.

Declare @Result int

SELECT @Result = count(1) 
 FROM   ::fn_listextendedproperty (N'MS_Description',  N'Schema',  'dbo', 
  N'Table',   '[Your Table Name]', 
 N'Column',  '[You Field Name]')


 
 IF @Result > 0 
 BEGIN

  EXEC sp_updateextendedproperty 
 @name = N'MS_Description', @value = 'Your comment sentences.',
 @level0type = N'Schema', @level0name = dbo, 
 @level1type = N'Table',  @level1name = [Your Table Name], 
 @level2type = N'Column', @level2name = [You Field Name];

 
 END
 ELSE 
 BEGIN


  EXEC sp_addextendedproperty 
 @name = N'MS_Description', @value = 'Your comment sentences.',
 @level0type = N'Schema', @level0name = dbo, 
 @level1type = N'Table',  @level1name = [Your Table Name], 
 @level2type = N'Column', @level2name = [You Field Name];

END

Basically, fn_listextendedproperty is a built-in function that can find out whether description was added or not. If count > 0, it implies that we need to update the Description so we can use sp_updateextendedproperty or else use sp_addextenededproperty to add a comment in it.

If you want to find out all the table view names, you can use the following SQL statement: 

select * from INFORMATION_SCHEMA.VIEWS

Points of Interest

You can use the above TSQL code and your preferred programming language such as C#, VB.NET etc., to develop your web datadict and view your MS SQL via web. That is very good.

For reference: http://msdn.microsoft.com/en-us/library/ms186778.aspx