"Go" MSSQL Gets the column name information for the specified table, description, data type, length

/*--function: query out the field according to the specific table name, as well as the description, data type, length, accuracy, whether it is self-increment, whether it is empty etc.--wonder qq:37036846 QQ Group:. NET Top Elite id:124766907--Time: 2011-03-23 11:25--Description: Creating a Stored Procedure-parameter: @tableName table name*/CREATE PROCSp_getlistscolumninfobytablename (@tableName nvarchar(255)) as BEGINSELECT  Case   whenQ.indid>=1  Then 'PRIMARY Key' ELSE "' ENDIs_keys,x.objname asColumnname,x.value asColumndescription,z.name asDatatype,y.max_length asLength, Y.Precision, Y.scale,y.is_identity,y.is_nullable from(SELECT * from:: Fn_listextendedproperty (NULL,'User','dbo','Table',@tableName,'column',default) )  asXINNER JOIN (SELECT * fromSys.all_columnswhere object_id=(Select object_id  fromSys.all_objectswhereName=@tableName))  asY onX.objname=Y.name Collate chinese_prc_ci_asInner Joinsys.systypes Z onZ.xusertype=y.user_type_id Left Join(Select * fromSysindexkeyswhereId=(Select object_id  fromSys.all_objectswhereName=@tableName)) asQ onQ.colid=y.column_idOrder  byy.column_idENDTo test the stored procedure:EXECSp_getlistscolumninfobytablename'UserInfo'

Transferred from: http://www.cnblogs.com/wanzegui325/archive/2011/03/23/GetListsColumnInfoByTableName.html