Obtain the names of all user tables in the SQL Server database and how to use SQL to obtain the names and structures of all tables in the database (column names and data types)

1. Obtain the names of all user tables in the SQL Server database.

Obtain all user tables: (xtype: U User table; V view; P Stored Procedure)

SQL = "select ID, name from sysobjects where xtype = 'U' and name <> 'dtproperties' order by name"

Obtain the names of all fields in a table:

Select syscolumns. Name as columnname, policypes. Name as type, syscolumns. length, syscolumns. isnullable
From sysobjects
Inner join syscolumns on sysobjects. ID = syscolumns. ID
Inner join policypes on syscolumns. xtype = policypes. xtype
Where (sysobjects. xtype = 'U ')
And (sysobjects. Name <> 'dtproperties ')
And (sysobjects. Name =' Tablename ')
And (policypes. Name <> 'sysname ')
And (policypes. Status <> 3) -- // 3 is used to exclude custom data types.
Group by syscolumns. Name, sysobjects. Name, syscolumns. xtype, policypes. Name, syscolumns. length, syscolumns. isnullable

Obtain the objects associated with a stored procedure:

Select name from sysobjects where ID in (
Select distinct BB. depid as kk
From sysobjects
Join sysdepends BB on BB. ID = sysobjects. ID
Where sysobjects. Name = 'spname'
)

<Note: Only tables related to select, such as tables used for update and insert, cannot be found.>

 

2. How to use SQL in SQL Server to obtain all the table names in the database and the table structure (column names and data types)

Create procedure gettableinfo
/* @ Tablename varchar (32 )*/
As

/* Create a temporary table */
Create Table # tablefields (
Tablename varchar (32 ),
Fieldname varchar (32 ),
Fieldtype varchar (32 ),
Fieldlength varchar (32 ),
Scale varchar (32 ),
Des varchar (256 ),
Defaultvalue varchar (32 ),
Cannull varchar (32)
)
/* Declare the cursor */
Declare table_cur scroll cursor
For select sysobjects. name from sysobjects where sysobjects. xtype = 'U'
For update of sysobjects. Name
/* Declare the temporary table name */
Declare @ tname varchar (32)
/* Open the cursor */
Open table_cur
Fetch next from table_cur into @ tname
While @ fetch_status = 0 begin
Select sysobjects. Name as tablename, syscolumns. Name as filedname,
Policypes. Name as fieldtype, syscolumns. length,
Syscolumns. Scale
Into # filedinfo_master
From syscolumns inner join
Policypes on syscolumns. xtype = policypes. xtype inner join
Sysobjects on syscolumns. ID = sysobjects. ID
Where (sysobjects. xtype = 'U') and (policypes. Name <> 'sysname') and sysobjects. Name = @ tname
/* Obtain the field Description */
Select objname as filedname, value into # filedinfo
From: fn_listextendedproperty ('Ms _ description', 'user ',
'Dbo', 'table', @ tname,
'Column ', default)


/* Obtain the default field value */
Select objname as filedname, value as defaultvalue
Into # filedinfo2
From: fn_listextendedproperty ('defaultvalue', 'user ',
'Dbo', 'table', @ tname,
'Column ', default)

/* Obtain whether the field can be blank */
Select objname as filedname, value as cannull
Into # filedinfo3
From: fn_listextendedproperty ('Ms _ allowbles', 'user ',
'Dbo', 'table', @ tname,
'Column ', default)



/* Join field description and attributes */
Insert into # tablefields
Select cast (# filedinfo_master.tablename as varchar (32 )),
Cast (# filedinfo_master.filedname as varchar (32 )),
Cast (# filedinfo_master.fieldtype as varchar (32 )),
Cast (# filedinfo_master.length as varchar (32 )),
Cast (# filedinfo_master.scale as varchar (32 )),
Cast (# filedinfo. [value] As varchar (256 )),
Cast (# filedinfo2.defaultvalue as varchar (32 )),
Cast (# filedinfo3.cannull as varchar (32 ))

From DBO. # filedinfo_master left Outer Join
DBO. # filedinfo on DBO. # filedinfo_master.filedname = DBO. # filedinfo. filedname
Left Outer Join DBO. # filedinfo2
On DBO. # filedinfo_master.filedname = DBO. # filedinfo2.filedname
Left Outer Join DBO. # filedinfo3
On DBO. # filedinfo_master.filedname = DBO. # filedinfo3.filedname
Where
# Filedinfo_master.tablename = @ tname
Fetch next from table_cur into @ tname
-- If exists (select * From DBO. sysobjects where id = object_id (n' # filedinfo_master ') and objectproperty (ID, n' isusertable') = 1)
Drop table # filedinfo_master
-- If exists (select * From DBO. sysobjects where id = object_id (n' # filedinfo ') and objectproperty (ID, n'isusertable') = 1)
Drop table # filedinfo
-- If exists (select * From DBO. sysobjects where id = object_id (n' # filedinfo2 ') and objectproperty (ID, n' isusertable') = 1)
Drop table # filedinfo2
Drop table # filedinfo3
End
Select * from # tablefields
Deallocate table_cur

Go
Set quoted_identifier off
Go
Set ansi_nulls on
Go