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.
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.
'SELECT ''Found in ?''
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.
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 @DataExists=1 from [' + table_name + '] where [' + column_name + '] like ''%' +@searchString + '%'''
WHERE data_type = 'varchar'
ORDER BY table_name, column_name
SELECT @i=1, @max = MAX(rowId)
SET @parameter = '@DataExists Bit OUTPUT'
WHILE @i < @max
SELECT @sql = searchSql
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
SELECT distinct tablename, columnname
WHERE datafound =1