Access to views, tables, and stored procedures in all databases of users through the system database _mssql

Copy Code code as follows:

--Set SQL to get instance name
sp_configure ' xp_cmdshell ', 1;
Go
Reconfigure;
Go

--Get Instance Name
EXEC sys. xp_cmdshell ' SQLCMD-LC '

--Get all databases
Select * from Master. sysdatabases ORDER BY Name

--Get all tables in the database
Use Yeekang---database name
SELECT * from sysobjects where type = ' U ' ORDER by name

--Gets the specified table field
SELECT * from syscolumns where id= object_id (' Userinfo ')

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 ordinal =a. Colorder,
Field name =a. Name,
Identify =case when ColumnProperty (A. ID, a.name, ' isidentity ') = 1 Then ' √ ' Else ' end,
Primary key =case when exists (SELECT 1 to sysobjects where xtype = ' PK ' 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,
Number of bytes =a. Length,
Length =columnproperty (a.id, A. Name, ' PRECISION '),
Number of decimal places =isnull (ColumnProperty (A. ID, a.name, ' Scale '), 0),
Allow null =case when a. isnullable= 1 Then ' √ ' Else ' end,
Default value =isnull (E.text, ""),
Field Description =isnull (G.[value], "")

Copy Code code as follows:

From Syscolumns A
Left join Systypes 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 A. id= object_id (' Userinfo ')
Order by A. Id,a. Colorder

Another example:

Each database in the SQL Server database has a sysobjects system table that stores all objects of the current database, including object tables, user tables, views, triggers, constraints, defaults, logs, and stored procedure letters.

Let's list some of the field names for this table:

Name object names
ID Object identification number
Xtype Object Type
User ID of the UID owner object
Parent_obj the object identification number of the parent object
Date the Crdate object was created
Ftcatid Full-text catalog identifiers for all user tables registered for Full-text indexing
Schema_ver version number,
Category for publishing, constraints, and identities

See the above field you should already know ...
Xtype This field is to determine the object type ...

If you want to get all of the table information in the database, you can write the following query statement:

Copy Code code as follows:

SELECT * from sysobjects where xtype= ' u '

If you want to get information about all the stored procedures in the database, you can write the following query:

Copy Code code as follows:

SELECT * from sysobjects where xtype= ' P '

If you want to get all the view information in the database, you can write the following query statement:

Copy Code code as follows:

SELECT * from sysobjects where xtype= ' V '

If you want to get all the trigger information in the database, you can write the following query statement:

Copy Code code as follows:

SELECT * from sysobjects where xtype= ' tr '

Get all SQL database names, all table names, all field names

1. Get all database names:

Select Name from Master. sysdatabases ORDER BY Name

2. Get all table names:

Copy Code code as follows:

Select Name from DatabaseName.. sysobjects Where xtype= ' U ' ORDER by Name

Xtype= ' U ': represents all user tables;

Xtype= ' S ': represents all system tables;

3. Get all field names:

Copy Code code as follows:

Select Name from syscolumns Where id=object_id (' tablename ')

Method Two.

Copy Code code as follows:

SELECT * FROM sys.databases
SELECT * FROM Information_schema.tables
SELECT * from Information_schema.columns where table_name= ' tablename '