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