python - check whether a server(s) has a database(s) with a table(s) that contain the information that I need on SQL server 2008 on win 7 -
i new sql server 2008 on win 7.
in daily job, need check whether server has database(s) table(s) contain info need.
for example,
on server_1, have 50 databases, each of them has tens or hundreds of tables. database_1 has 30 tables names table_1, table_2, … , table_30 database_2 has 50 tables names table_1, table_2, … , table_50 database_3 has 80 tables names table_1, table_2, … , table_80 need find whether these tables have columns need, such , my_column_1 my_column_2 my_column_3 ... my_column_20 on server_2, have 70 databases, , similar number of tables on each database. may have access 10 servers searching information.
sometimes, not know column names because cannot search each table manually.
i want find info need. example,
the info employee_id , emplyee_address column may not named suppose.
i can access other servers in ms sql server management studio in order find need, but, how locate info need in many databases , servers efficiently ?
i can access databases through python 3.2.5 eclipse.
any help or suggestions appreciated.
you can iterate through databases on single server instance using sp_msforeachdb
, , in catalogue view sys.all_columns
(in tables) given columns, so:
declare @command nvarchar(max); set @command = n'use [?]; select db_name() dbname, object_name(object_id) tablename, name columnname sys.all_columns name in (''my_column_1'', ''my_column_2'');'; exec sp_msforeachdb @command;
however, @ server + instance level, afaik need maintain collection of sqlserver server , instance connection strings , manually connect each instance appropriate credentials in order execute above. might able utilize sqlserver browser service 'find' sqlserver instances on lan, although requires service running , security logins each still required.
edit re: avoiding multiple result sets
borrowing aaron bertrand's thought here, , given #temp tables scope connection, can accumulate each result in #temp
table, so:
set nocount on; create table #tmp(dbname nvarchar(50), tablename nvarchar(50), columnname nvarchar(50)); exec sp_msforeachdb 'use [?]; insert #tmp(dbname, tablename, columnname) select db_name() dbname, object_name(object_id) tablename, name columnname sys.all_columns name in (''my_column_1'', ''my_column_2'');'; select * #tmp; drop table #tmp;
(and aaron's blog recommends against using sp_msforeachdb
both undocumented , potentially buggy, , provides alternative implementation)
python sql sql-server database sql-server-2008
No comments:
Post a Comment