"SQL statements"-ctrl+3 stored procedures for querying table properties [Sp_select_talberowname]

/**-- =============================================
Author:xft
Cteatedate:2013-10-11
Description: View full field information for a 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 join Sys.extended_properties B on a.id=b.major_id and a.colid = b.minor_id
----Where a.id=object_id (' [email protected]+ ') '

--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 ("[Email protected]+")) 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= "[email protected]+") as C
--on e.name=c.name) '

--print @sql
--exec (@sql)
DECLARE @sql Nvarchar (2000)
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 index_id from sys.index_columns WHERE object_id = a.object_id and index_column_id=a.column_id
) Then ' + ' + ' + ' is ' + ' + ' + ' + ' + ' + ' + ' + ' + ' end,
Allow empty =case when a.is_nullable=1 then ' + ' + ' + ' is ' + ' + ' + ' + ' + ' + ' + ' + ' + ' + ' + ' + ' + ' end,
Default value =isnull (e.text, ' + ' + ' + ' + '),
Field Description =isnull (G.[value], ' + ' + ' + ' + ')
From Sys.columns A
Left join Sys.types B on a.user_type_id=b.user_type_id
INNER JOIN Sys.tables D on a.object_id=d.object_id
Left join Sys.syscomments E on a.default_object_id=e.id
Left joins Sys.extended_properties G on a.object_id=g.major_id and a.column_id=g.minor_id
Left join Sys.extended_properties F on d.object_id=f.major_id and f.minor_id=0
WHERE d.name= ' + ' [email protected]+ ' + ' ORDER by d.name,a.column_id '
EXEC sp_executesql @sql
END
GO

"SQL statements"-ctrl+3 stored procedures for querying table properties [Sp_select_talberowname]