SQL Server: Finding a table in multiple databases.

Just a quick one – I needed to find which databases held a particular table:

EXEC sp_msforeachdb 'use [?] SELECT ''?'' as [datbaseName],SCHEMA_NAME(schema_id) as [schemaName], name as [tableName]
FROM sys.tables where name = ''MY_TABLE'''

Just replace MY_TABLE with the name of the table you’re looking for.

Advertisements

SQL Server: Finding a column in multiple databases.

My client has many instances of the same database in various versions. The version I’m developing against is missing a field expected by a c# class, and I can’t find a script to create the column.

I came up with the following SQL Server query to allow me to search all the instances of the database to find one that contained the field.

EXEC sp_MSforeachdb 
 'SELECT ''Found in ?''
 FROM sys.columns 
 WHERE Name = N''FieldName''
 AND Object_ID = Object_ID(N''TableName'')'

Replace the field name and the table name with the values you’re looking for and it will search all the databases on the server for  a match.

SQL Server – searching all columns and tables

I’ve inherited a legacy app at work, the developer having gone on to pastures new. The handover was a little light in details especially in the area of the database. Of course, the first bit of work that comes in requires a change to a value that’s held in different columns in different tables in the database, and I have to find them.

Therefore with a little bit of work I came up with this:

DECLARE @tempTable TABLE(rowId INT IDENTITY(1,1), tableName sysName, columnName sysName, searchSql VARCHAR(2000), dataFound BIT)
DECLARE @i INT
DECLARE @max INT
DECLARE @DataExists BIT
DECLARE @sql NVARCHAR(2000)
DECLARE @parameter NVARCHAR(2000)
DECLARE @searchString varchar(2000)

set @searchString = 'Text To Find'

INSERT INTO @tempTable(tablename, columnName, searchSql)
SELECT      table_name, 
            column_name, 
            'select @DataExists=1 from [' + table_name + '] where [' + column_name + '] like ''%' +@searchString + '%'''
  FROM information_schema.columns
WHERE data_type = 'varchar'
  ORDER BY table_name, column_name
  
SELECT @i=1, @max = MAX(rowId)
FROM @tempTable  
 
SET @parameter = '@DataExists Bit OUTPUT'

WHILE @i < @max
BEGIN
      SELECT @sql = searchSql 
        FROM @tempTable 
       WHERE rowId = @i
      
       set @dataExists = 0
      
       EXEC sp_executeSql @sql, @parameter, @DataExists = @DataExists OUTPUT
      IF @DataExists = 1
            UPDATE @tempTable SET DataFound = 1 WHERE rowId = @i
            
      SET @i = @i + 1
      
END

SELECT distinct tablename, columnname
  FROM @temptable 
 WHERE datafound =1