There are too many tables in the database. It is difficult to view the structure of a table, so it is easier to use this stored procedure;
CRM
/* **************************************** ***
* Obtain table information based on the table name, including field descriptions.
**************************************** *** */
Create Proc [ DBO ] . [ Sp_help_table ]
( @ Tablename Varchar ( 200 ), @ Columnlike Varchar ( 200 ) = Null )
As
-- If the table name does not exist, select a similar table.
If Not Exists (
Select 1
From Sysobjects
Where ID = Object_id (@ Tablename )
And Type = ' U '
)
Begin
Select Name From Sysobjects
Where Name Like ' % ' + @ Tablename + ' % ' And Type = ' U '
Return
End
-- Filter similar column names
If ( @ Columnlike Is Null )
Set @ Columnlike = ''
Declare @ Columntable Table (Cname Varchar ( 200 ))
Insert @ Columntable
(
Cname
)
Select A. Name
From Syscolumns A, sysobjects d
Where A. ID = D. id
And D. Name = @ Tablename
And A. Name Like ' % ' + @ Columnlike + ' % '
-- Query table structure information
Select Table Name= Case
When A. colorder = 1 Then D. Name
Else ''
End ,
Table description = Case
When A. colorder = 1 Then Isnull (F. value,'' )
Else ''
End ,
FIELD No. = A. colorder,
Field name = A. Name,
Field description= Isnull (G. [ Value ] , '' ),
Identifier = Case
When Columnproperty (A. ID, A. Name, ' Isidentity ' ) = 1 Then ' √ '
Else ''
End ,
Primary Key = Case
When Exists (
Select 1 From SysobjectsWhere Xtype = ' PK ' And Parent_obj = A. ID
And Name In ( Select Name From Sysindexes
Where Indid In ( Select Indid From Sysindexkeys
Where ID = A. ID And Colid = A. colid ))
)Then ' √ '
Else ''
End ,
Type = B. Name,
Bytes occupied = A. length,
Length = Columnproperty (A. ID, A. Name, ' Precision ' ),
Decimal places = Isnull ( Columnproperty (A. ID, A. Name, ' Scale ' ),0 ),
Null allowed = Case When A. isnullable = 1 Then ' √ '
Else ''
End ,
Default Value = Isnull (E. Text , '' )
From Syscolumns
Left Join Policypes B
On A. xusertype = B. xusertype
Inner Join Sysobjects d
On A. ID = D. id
And D. xtype = ' U '
And D. Name <> ' Dtproperties '
Left Join Syscomments E
On A. cdefault = E. ID
Left Join SYS. extended_properties g
On A. ID = G. major_id
And A. colid = G. minor_id
Left Join SYS. extended_properties F
On D. id = F. major_id
And F. minor_id= 0
-- Where D. Name = 'table to be query' -- if only the specified table is queried, add this condition.
Where D. Name = @ Tablename
And Exists (
Select 1
From @ Columntable
Where Cname = A. Name
)
Order By A. ID, A. colorder