Reads the structure information of tables in the database.

/*************************************** ******
Reads the structure information of tables in the database.
**************************************** *****/
Create procedure getalltableinfo
Begin

Declare @ strname nvarchar (128), @ TID int, @ strowner varchar (50) --, @ au_id varchar (11)
Set nocount on
-- Get all user table information

Declare all_tables cursor
For select convert (nvarchar (128), name), ID from sysobjects where objectproperty (ID, N 'isusertable') = 1 and objectproperty (ID, n' ismsshipped ') = 0 order by name

Create Table # colinfo
(
Tname nvarchar (50), -- table name
Colname nvarchar (50), -- column name
Colid smallint, -- column ID
Remark nvarchar (1000), -- Comment
Type varchar (50), -- Data Type
Defaultvalue nvarchar (255), -- Default Value
Isiden nchar (1), -- whether to identify the column
Isallownull nchar (1), -- whether to allow null
Isprimarykey nchar (1) -- whether it is a primary key
)

-- Primary key information
Create Table # pkinfo
(
Table_qualifier nvarchar (50 ),
Table_owner varchar (50 ),
Table_name nvarchar (50 ),
Column_name nvarchar (50 ),
Key_seq int,
Pk_name nvarchar (50)
)

Set @ strowner = user_name ()

Open all_tables
Fetch next from all_tables into @ strname, @ tid
While @ fetch_status = 0
Begin
-- Insert table and comment information
Insert into # colinfo
Select @ strname, '', 0, isnull (convert (nvarchar (1000), value ),''),'','','','',''
From (select @ strname tname) A left join
: Fn_listextendedproperty ('Ms _ description', N 'user', N 'dbo', N 'table', @ strname, null, null) -- Comment information of a table
On 1 = 1
-- Insert column information
Insert into # colinfo
Select @ strname, C. Name cname, C. colid, isnull (convert (nvarchar (1000), R. value ),''),
(Case when (C. xtype> = 34 and C. xtype <= 58) or C. xtype in (99,104,127,189,) Then T. Name
When C. xtype in (59,60, 106,108,122) Then T. name + '(' + convert (varchar, C. xprec) + ',' + convert (varchar, C. XScale) + ')'
Else T. Name + '(' + convert (varchar, C. Prec) + '/' + convert (varchar, C. Length) + ')' end ),
Isnull (def. Text ,''),
(Case C. status when 0x80 then '√ 'else' 'end ),
Case isnullable when 1 then' is 'else' no' end,
''
From (policypes t
Inner join
(Syscolumns C
Left join syscomments def
On C. cdefault = def. ID and 1 = def. colid
) On T. xtype = C. xtype and T. xusertype = C. xusertype
)
Left join
: Fn_listextendedproperty ('Ms _ description', N 'user', N 'dbo', N 'table', @ strname, N 'column', null) r -- comment of a column
On convert (varchar, R. objname) = convert (varchar, C. Name)
Where C. ID = object_id (@ strname)
Order by C. colid

Insert into # pkinfo exec [DBO]. [sp_pkeys] @ strname, @ strowner, default

Fetch next from all_tables into @ strname, @ tid
End

-- Update primary key information
Update # colinfo
Set isprimarykey = '*'
From # pkinfo
Where # pkinfo. table_name = # colinfo. tname and # pkinfo. column_name = # colinfo. colname

Close all_tables
Deallocate all_tables

Select '☆*** ☆'objectname, ''remark, ''col1, ''col2, ''col3, ''col4, ''col5, tname tablename, 0 flag, colid
From # colinfo
Where colname =''

Union
Select 'table name: '+ tname, remark, '', tname, 1 flag, colid
From # colinfo
Where colname =''

Union
Select 'column name', 'annotate', 'Data type', 'default', 'identifi', 'allowed null', 'Primary key', tname, 2, colid
From # colinfo
Where colname =''

Union
Select colname, remark, type, defaultvalue, isiden, isallownull, isprimarykey, tname, 3, colid
From # colinfo
Where colname <>''

Order by 8, 9, 10

Drop table # pkinfo
Drop table # colinfo

End
Go
 
 

/*************************************** ******
Reads the structure information of tables in the database.
**************************************** *****/
Create procedure gettableinfo @ tablename nvarchar (128)
Begin

Declare @ strname nvarchar (128), @ TID int, @ strowner varchar (50) --, @ au_id varchar (11)

Set nocount on

-- Get all user table information

Declare all_tables cursor
For select convert (nvarchar (128), name), ID from sysobjects where objectproperty (ID, N 'isusertable') = 1 and objectproperty (ID, n' ismsshipped ') = 0 and object_name (ID) = @ tablename order by name

Create Table # colinfo
(
Tname nvarchar (50), -- table name
Colname nvarchar (50), -- column name
Colid smallint, -- column ID
Remark nvarchar (1000), -- Comment
Type varchar (50), -- Data Type
Defaultvalue nvarchar (255), -- Default Value
Isiden nchar (1), -- whether to identify the column
Isallownull nchar (1), -- whether to allow null
Isprimarykey nchar (1) -- whether it is a primary key
)

-- Primary key information
Create Table # pkinfo
(
Table_qualifier nvarchar (50 ),
Table_owner varchar (50 ),
Table_name nvarchar (50 ),
Column_name nvarchar (50 ),
Key_seq int,
Pk_name nvarchar (50)
)

Set @ strowner = user_name ()

Open all_tables
Fetch next from all_tables into @ strname, @ tid
While @ fetch_status = 0
Begin
-- Insert table and comment information
Insert into # colinfo
Select @ strname, '', 0, isnull (convert (nvarchar (1000), value ),''),'','','','',''
From (select @ strname tname) A left join
: Fn_listextendedproperty ('Ms _ description', N 'user', N 'dbo', N 'table', @ strname, null, null) -- Comment information of a table
On 1 = 1
-- Insert column information
Insert into # colinfo
Select @ strname, C. Name cname, C. colid, isnull (convert (nvarchar (1000), R. value ),''),
(Case when (C. xtype> = 34 and C. xtype <= 58) or C. xtype in (99,104,127,189,) Then T. Name
When C. xtype in (59,60, 106,108,122) Then T. name + '(' + convert (varchar, C. xprec) + ',' + convert (varchar, C. XScale) + ')'
Else T. Name + '(' + convert (varchar, C. Prec) + '/' + convert (varchar, C. Length) + ')' end ),
Isnull (def. Text ,''),
(Case C. status when 0x80 then '√ 'else' 'end ),
Case isnullable when 1 then' is 'else' no' end,
''
From (policypes t
Inner join
(Syscolumns C
Left join syscomments def
On C. cdefault = def. ID and 1 = def. colid
) On T. xtype = C. xtype and T. xusertype = C. xusertype
)
Left join
: Fn_listextendedproperty ('Ms _ description', N 'user', N 'dbo', N 'table', @ strname, N 'column', null) r -- comment of a column
On convert (varchar, R. objname) = convert (varchar, C. Name)
Where C. ID = object_id (@ strname)
Order by C. colid

Insert into # pkinfo exec [DBO]. [sp_pkeys] @ strname, @ strowner, default

Fetch next from all_tables into @ strname, @ tid
End

-- Update primary key information
Update # colinfo
Set isprimarykey = '*'
From # pkinfo
Where # pkinfo. table_name = # colinfo. tname and # pkinfo. column_name = # colinfo. colname

Close all_tables
Deallocate all_tables

Select '☆*** ☆'objectname, ''remark, ''col1, ''col2, ''col3, ''col4, ''col5, tname tablename, 0 flag, colid
From # colinfo
Where colname =''

Union
Select 'table name: '+ tname, remark, '', tname, 1 flag, colid
From # colinfo
Where colname =''

Union
Select 'column name', 'annotate', 'Data type', 'default', 'identifi', 'allowed null', 'Primary key', tname, 2, colid
From # colinfo
Where colname =''

Union
Select colname, remark, type, defaultvalue, isiden, isallownull, isprimarykey, tname, 3, colid
From # colinfo
Where colname <>''

Order by 8, 9, 10

Drop table # pkinfo
Drop table # colinfo

End
Go