Shulou(Shulou.com)05/31 Report--

It is believed that many inexperienced people do not know what to do about how to quickly query a table information between multiple databases in sql server. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

1. First, learn about the use of system stored procedures and system tables, and briefly introduce several system stored procedures I used (reference network).

Use master-switch to the system database Because most of the system stored procedures and system tables used below exist in the database goexec sp_helpdb-- query all databases of the current server select [name] from [sysdatabases]-query all databases of the current server select * from sysobjects where type = 'upright from information_schema.tables]-list all the table names in the current database * from information_schema.tables-list the current database All the table names in (execute and compare the query results with the above statement) select * from syscolumns where id = object_id ('spt_fallback_db')-lists all the information in the specified table Including fields, etc. (modify parameters as needed)

two。 Go to the code directly (please refer to the comments for details, it is purely for learning, please point out any mistakes)

Use master-switch to the system database Because most of the system stored procedures and system tables used below exist in the database go- queries multiple databases based on the name of the table at the current server to get information about which database exists the table-declare @ DataBaseName nvarchar (max)-defines variables ( Name of the database) declare cur cursor for select [name] from [sysdatabases]-- defines the cursor The cursor points to the list of all database names on the current server open cur-- opens the cursor create table # TableInfo (table_catalog nvarchar (max), table_schema nvarchar (max), table_name nvarchar (max), table_type nvarchar (max))-- creates a temporary table to store all the table information of all databases fetch next from cur into @ DataBaseName-- gets the data of the cursor Equivalent to getting the first piece of data in the database name list while (@ @ fetch_status=0) beginprint''print' 'print' current database:'+ @ DataBaseName-- read out 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'- -'fetch next from cur into @ DataBaseName-- cursor mobile endclose cur-- close cursor deallocate cur-- release cursor print' 'declare @ TableName nvarchar (max) set @ TableName =' MyTableName'-- query condition (root from as needed If exists (select table_name from # TableInfo where table_name = @ TableName)-- query the table with the specified name beginprint'= the'+ @ TableName + 'table exists on the current server For related information, please go to the result window to view the database to which 'select table_catalog as' belongs, and the table name 'table_name as' is' from # TableInfo where table_name = @ TableName-- relevant information about the output table. From this information, you can know which database the table is in endelsebeginprint'- there is no'+ @ TableName + 'table on the current server -' enddrop table # TableInfo-- delete the temporary table and read the above Have you mastered the method of how to quickly query a table information between multiple databases in sql server? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.