SQL server allows you to quickly query information about a table among multiple databases.

SQL server allows you to quickly query information about a table among multiple databases.

This example describes how SQL server can quickly query information of a table among multiple databases. We will share this with you for your reference. The details are as follows:

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 this database goexec 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 (perform a comparison with the preceding statement) query results) 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 ------------------ the current server queries multiple databases based on the table name to obtain information about which database has the table ---------------- declare @ DataBaseName nvarchar (max) -- Define the variable (Database name) declare cur cursor for select [name] from [sysdatabases] -- Define the cursor, this cursor points to the list of all database names on the current server open cur -- open the 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 all table information of all databases. fetch next from cur into @ DataBaseName -- Obtain cursor data, it is equivalent to obtaining the first data in the database name list while (@ fetch_status = 0) beginprint ''print ''current database: '+ @ DataBaseName -- read the name of each database insert into # TableInfo -- insert the data queried in the stored procedure into the temporary table exec ('select table_catalog, table_schema, table_name, table_type from '+ @ DataBaseName + '. information_schema.tables ') -- Query all the tables in the corresponding database. print 'example ------- 'fetch next from cur into @ DataBaseName -- move the cursor endclose cur -- close the cursor deallocate cur -- release the cursor print ''print ''' print' 'print ''print ''declare @ TableName nvarchar (max) set @ TableName = 'mytablename' -- Query condition (modified as needed) if exists (select table_name from # TableInfo where table_name = @ TableName) -- query the beginprint '================================ the current server has' + @ 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 the database in which the table is located, endelsebeginprint '------------------ the current server does not exist' + @ TableName + 'table ---------------- 'enddrop table # TableInfo -- delete a temporary table