Sqlserver quickly queries the information of a table among multiple databases

Welcome to the Windows community forum and interact with 3 million technical staff for a recent internship. The company's server has more than a dozen databases. To help you quickly find the corresponding database based on the table name of a data table, I reviewed the cursor knowledge and wrote the following SQL code to facilitate my work. 1. First, let's take a look at the system stored procedures and systems.

Welcome to the Windows community forum and interact with 3 million technical staff> enter the internship recently. The company's server has more than a dozen databases, in order to quickly find the corresponding database based on the table name of a data table, I reviewed the cursor knowledge and wrote the following SQL code to facilitate my work. 1. First, let's take a look at the system stored procedures and systems.

Welcome to the Windows community forum and interact with 3 million technicians>

My internship recently showed that my company's server has more than a dozen databases. In order to quickly find the corresponding database based on the table name of a data table, I reviewed the cursor knowledge, write the following SQL code to facilitate your work.

1. First, let's take a look at the use of system stored procedures and system tables, and briefly introduce several system stored procedures I have used (refer to the network)

Use master -- switch to the system database because most of the system stored procedures and system tables used below exist in the Database

Go

Exec sp_helpdb -- Query all databases on the current server

Select [name] from [sysdatabases] -- Query all databases on the current server

Select * from sysobjects where type = 'U' -- list all the table names in the current database

Select * from information_schema.tables -- list all the table names in the current database (execute a comparison with the query results of the preceding statement)

Select * from syscolumns where id = object_id ('spt _ fallback_db ') -- list all information in the specified table, including fields (modify parameters as needed)

2. directly add the code (For details, refer to the notes, which are purely learning. If there are any mistakes, please point them out)

Use master -- switch to the system database because most of the system stored procedures and system tables used below exist in the Database

Go

------------------ On the current server, query multiple databases based on the table name to obtain the information about which database has the table ------------------

Declare @ DataBaseName nvarchar (max) -- defines the variable (database name)

Declare cur cursor for select [name] from [sysdatabases] -- defines a cursor that points to the list of all database names on the current server

Open cur -- open cursor

Create table # TableInfo (table_catalog nvarchar (max), table_schema nvarchar (max), table_name nvarchar (max), table_type nvarchar (max )) -- create a temporary table to store information about all tables in all databases

Fetch next from cur into @ DataBaseName -- get the cursor data, equivalent to getting the first data in the database name list

While (@ fetch_status = 0)

Begin

Print''

Print''

Print 'current Database: '+ @ DataBaseName -- read the name of each database

Insert into # TableInfo -- insert the data queried by the stored procedure into the temporary table

Exec ('select table_catalog, table_schema, table_name, table_type from '+ @ DataBaseName +'. information_schema.tables ') -- Query all tables in the corresponding database

Print 'Certificate '--------------------------------------------------------------------------------------------------------------------------------------'

Fetch next from cur into @ DataBaseName -- move the cursor

End

Close cur -- close the cursor

Deallocate cur -- release cursor

Print''

Print''

Print''

Print''

Print''

Declare @ TableName nvarchar (max)

Set @ TableName = 'mytablename' -- Query condition (modify as needed)

If exists (select table_name from # TableInfo where table_name = @ TableName) -- query the table with the specified name

Begin

Print '============================== the current server has a' + @ TableName + 'table, for more information, see the result window ======================='

Select table_catalog as 'database ', table_name as 'table name' from # TableInfo where table_name = @ TableName -- Information about the output table. from this information, you can know which database the table belongs.

End

Else

Begin

Print '-------------------- the current server does not exist' + @ TableName + 'table --------------------'

End

Drop table # TableInfo -- delete a temporary table