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

Question

I am new to SQL server 2008 on win 7.

In my daily job, I always need to check whether a server has a database(s) with a table(s) that contain the information that I need.

For example,

On server_1, I have 50 databases, 
each of them has tens or hundreds of tables.

Database_1 has 30 tables with names as table_1, table_2, … , table_30
Database_2 has 50 tables with names as table_1, table_2, … , table_50
Database_3 has 80 tables with names as table_1, table_2, … , table_80 

I need to find whether these tables have some columns that I need, 
Such as , 
    my_column_1 
    my_column_2
    my_column_3
    ...
    my_column_20

    On server_2, I have 70 databases, and similar number of tables on each database.
    I may have to access about 10 servers for searching my information.

Sometimes, I do not know the column names because I cannot search each table manually.

I just want to find the information that I need. For example,

The information about employee_id and emplyee_address
The column may not be named like what I suppose.

I can access other servers in MS SQL server management studio in order to find what I need, But, how to locate the information that I need in many databases and servers efficiently ?

I can also access the databases through python 3.2.5 from Eclipse.

Any help or suggestions would really appreciated.

You want to list the columns of each and every table or jst a single table in all servers. — Azar, Jun 20 '14 at 04:19
If you don't know the column names *a priori*, how can you tell if the database has it? — Gabe, Jun 20 '14 at 04:24
@Gabe, I only know what kind of information that I need to get, but, I do not know the exact names of columns in so many tables. thanks ! — user3601704, Jun 20 '14 at 16:29

score 1 · Accepted Answer · edited Jul 29 '20 at 18:37

You can iterate through all databases on a single server instance using sp_MSforeachdb, and then look in the catalogue view sys.all_columns (in all tables) for the given columns, like so:

DECLARE @command NVARCHAR(max); 
SET @command = 
  N'use [?]; 
    select db_name() as DBName, object_name(object_id) as TableName, 
           name as ColumnName 
    from sys.all_columns where name in (''my_column_1'', ''my_column_2'');';
EXEC sp_MSforeachdb @command;

However, at the server + instance level, AFAIK you will need to keep a collection of all SqlServer server and instance connection strings yourself and manually connect to each instance with appropriate credentials in order to execute the above. You might be able to use the SqlServer browser service to 'find' SqlServer instances on your LAN, although this requires that the service running and security logins to each will still required.

Edit
Re: Avoiding multiple result sets

Borrowing from Aaron Bertrand's idea here, and given that #temp tables are scope to the connection, you can accumulate each result in a #temp table, like so:

SET NOCOUNT ON;
CREATE TABLE #Tmp(DBName NVARCHAR(50), TableName NVARCHAR(50), ColumnName NVARCHAR(50));

EXEC sp_MSforeachdb
'USE [?];
INSERT INTO #Tmp(DBName, TableName, ColumnName) 
  SELECT db_name() as DBName, object_name(object_id) as TableName, name as ColumnName 
  FROM sys.all_columns
  WHERE name in (''my_column_1'', ''my_column_2'');';
 
SELECT * FROM #Tmp;
DROP TABLE #Tmp;

(And Aaron's blog also recommends against using sp_MSforeachdb as it is both undocumented and potentially buggy, and provides an alternative implementation)

thanks , it works, but, it returns all database, tables even though they are empty. How to return only the db, tables with the columns that I need ? — user3601704, Jun 20 '14 at 19:57
I've updated, although the linked reference recommends not to bake `sp_MSforeachdb` into production code. — StuartLC, Jun 21 '14 at 06:20

score 1 · Answer 2 · answered Jun 20 '14 at 04:20

In SQL Server there are system tables and views from where you can get the definition of the table structures. e.g. if you run the following query, you'll see all the field names of all the tables:

select * from sys.system_columns

You can refer to this link for more details on system tables

http://technet.microsoft.com/en-us/library/aa260604(v=sql.80).aspx

score 1 · Answer 3 · answered Jun 20 '14 at 04:28

If you want from all servers and databases : loop through server and databases from server and datbase tables.

Select * from sys.databases

Select * from sys.servers

Select object_name(c.object_id),name

from sys.all_columns c

join sys.tables t

on t.object_id = c.object_id

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

3 Answers3