if exists (select 1 from sysobjects where name = ' Sysproperties ' and xtype = ' V ')
Begin
DROP VIEW sysproperties
End
GO
CREATE VIEW sysproperties
As
SELECT class as id,minor_id as smallid,* from sys.extended_properties
-------Get the table structure in SQL Server 2005, execute the above statement first
SELECT
Table name = Case A.colorder If 1 then c.name ELSE ' END,
Order = A.colorder,
Field name = A.name,
Identification = Case ColumnProperty (a.id,a.name, ' isidentity ') when 1 Then ' √ ' ELSE ' END,
Primary KEY = case
When EXISTS (
SELECT *
From sysobjects
WHERE xtype= ' PK ' and name in (
SELECT Name
From sysindexes
WHERE Id=a.id and Indid in (
SELECT indid
From Sysindexkeys
WHERE Id=a.id and Colid in (
SELECT colid
From syscolumns
WHERE Id=a.id and Name=a.name
)
)
)
)
Then ' √ '
ELSE '
END,
Type = B.name,
Number of bytes = A.length,
Length = ColumnProperty (a.id,a.name, ' Precision '),
decimal = Case ISNULL (columnproperty (a.id,a.name, ' scale '), 0)
When 0 Then '
ELSE CAST (ColumnProperty (a.id,a.name, ' scale ') as VARCHAR)
END,
Allow NULL = case a.isnullable time 1 Then ' √ ' ELSE ' END,
Default value = ISNULL (D.[text], "),
Description = ISNULL (E.[value], ")
From Syscolumns A
Left JOIN systypes B on A.xtype=b.xusertype
INNER JOIN sysobjects C on a.id=c.id and c.xtype= ' U ' and c.name<> ' dtproperties ' and c.name= ' here instead of the table name you get '
Left JOIN syscomments D on a.cdefault=d.id
Left joins Sysproperties E on a.id=e.id and A.colid=e.smallid
ORDER by C.name, A.colorder
Get SQL Server database table structure