/**--============================================= author:xftcteatedate:2013-10-11description: View the full field information for the table remark: Modifyhistory: Modified information such as field length and identity added **/--============================================= CREATE PROCEDURE [dbo]. [Sp_select_talberowname] (@tablename varchar (max)) As BEGIN SET NOCOUNT on; --declare @sql varchar (max)--set @[email protected]----Set @sql = ' Select A.name,b.value----from Sys.syscolumns a Left joins Sys.extended_properties B on a.id=b.major_id and a.colid = b.minor_id----Where a.id=object_id ("' [email p rotected]+ ') '--set @sql = ' Select E.name,c.[type],e.value from ((--select a.name,b.value--from sys.syscolumns a left Jo In Sys.extended_properties B on a.id=b.major_id and a.colid = b.minor_id--where a.id=object_id ("' [email protected ]+ ")) as E--left join (--select sys.columns.name,sys.types.name as [type] from Sys.columns,sys.tables,sys.types--wher E sys.tables.object_id=sys.columns.object_id and sys.types.user_type_id=sys.columns.user_type_id--and sys.tables.name= "[email protected]+") as C--on e.name=c.name) '--print @sql--exec (@sql) DECLARE @sql Nvarch AR (+) SET @sql = ' Select field ordinal =a.column_id, field name =a.name, type =b.name, length =a.max_length, number of decimal digits =a.scale, identify =case when A.is_ Identity = 1 Then ' + ' + ' + ' is ' + ' + ' + ' + ' + ' + ' + ' + ' + ' + ' end, primary key =case when exists (SELECT 1 from sys.objects where type= ' + ' + ' PK ' + ' + ' + ' and name in (select name from Sys.indexes WHERE index_id in (select in dex_id from sys.index_columns WHERE object_id = a.object_id, and column_id=a.column_id)) Then ' + ' + ' + ' + ' + ' + ' + ' + ' + ' + ' + ' + ' + ' end, allow null =case when a.is_nullable=1 then ' + ' + ' is ' + ' + ' + ' + ' + ' + ' + ' + ' + ' + ' + ' + ' + ' + ' + ' + ' + ' end, Default value =isnull (e.text, ' + ' + ' + ' + ' + '), field Description =isnull (G.[value], ' + ' + ' + ' + ' + ') from Sys.columns Aleft join Sys.types B on A.user_type_id=b.user_type_idinner join Sys.tables D on a.object_id=d.object_id left join Sys.sys Comments E on A.default_obJect_id=e.idleft join Sys.extended_properties G on a.object_id=g.major_id and A.column_id=g.minor_idleft J Oin sys.extended_properties F on d.object_id=f.major_id and f.minor_id=0where d.name= ' + ' [email protect ed]+ ' + ' ORDER by d.name,a.column_id ' EXEC sp_executesql @sql END GO
"SQL statements"-ctrl+3 stored procedures for querying table properties