/**-- ============================================= Author:xftCteateDate:2013-10-11Description:查看表的完整字段信息Remark:ModifyHistory:修改 添加了字段長度和標識等信息**/-- ============================================= CREATE PROCEDURE [dbo].[sp_select_talberowName] (@tablename varchar(max))

AS BEGIN SET NOCOUNT ON; --declare @sql varchar(max) --set @tablename=@tablename ----set @sql = 'select a.name,b.value ----from sys.syscolumns a left join sys.extended_properties b on a.id=b.major_id AND a.colid = b.minor_id ----Where a.ID=OBJECT_ID('''+@tablename+''')'

--set @sql='select e.name,c.[type],e.value from (( --select a.name,b.value --from sys.syscolumns a left join sys.extended_properties b on a.id=b.major_id AND a.colid = b.minor_id --Where a.ID=OBJECT_ID('''+@tablename+''')) as e --left join ( --select sys.columns.name,sys.types.name as [type] from sys.columns,sys.tables,sys.types --where sys.tables.object_id=sys.columns.object_id and sys.types.user_type_id=sys.columns.user_type_id --and sys.tables.name='''+@tablename+''') as c --on e.name=c.name)'

--print @sql --exec (@sql) DECLARE @sql Nvarchar(2000)SET @sql = 'SELECT字段序號=a.column_id,字段名=a.name,類型=b.name,長度=a.max_length,小數位數=a.scale,標識=case when a.is_identity = 1 THEN '+''''+'是'+''''+' else '+ ''''+''+'''' +' end,主鍵=case when exists(SELECT 1 FROM sys.objects where type='+''''+'PK'+''''+' and name in (SELECT name FROM sys.indexes WHERE index_id in(SELECT index_id FROM sys.index_columns WHERE object_id = a.object_id AND index_column_id=a.column_id))) then '+''''+'是'+''''+' else '+''''+''+''''+'end,允許空=case when a.is_nullable=1 then '+''''+'是'+''''+' else '+''''+''+''''+' end,默認值=isnull(e.text,'+''''+''+''''+'),字段說明=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.syscomments 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 join sys.extended_properties f on d.object_id=f.major_id and f.minor_id=0WHERE d.name='+''''+@tablename+''''+'order by d.name,a.column_id'EXEC sp_executesql @sql END GO