This stored procedure takes two parameters:
1. Column name to be searched within all tables
2. string/value which needs to be searched within all tables.
CREATE PROCEDURE usp_SearchAllTables_InExistingDB
(
@colName varchar(100),
@colValue varchar(1000)
)
As
BEGIN
-- Copyright © 2011 Vadivel Mohanakrishnan. All rights reserved.
-- Purpose: To search a particular column of all tables for a given search string
-- Written by: Vadivel Mohanakrishnan
-- Site: http://vadivel.blogspot.com
-- Tested on: SQL Server 2005
SET NOCOUNT ON
--VARIABLE DECLARATION
Declare @TableName nvarchar(100)
Declare @ColumnName nvarchar(100)
Declare @RowNum int
Declare @searchString varchar(1000)
Declare @columnToSearch varchar(100)
--ASSIGN SP PARAMETER VALUE TO LOCAL VARIABLE
Set @searchString = @colValue
Set @ColumnToSearch = @colName
--TEMP TABLE TO HOLD THE FINAL RESULT!
CREATE TABLE #SearchResults
(
TableName VARCHAR(1000),
RecordCount INT
)
--TEMP TABLE TO HOLD THE TABLENAMES CONTAINING
-- COLUMNNAME WE ARE LOOKING FOR!
CREATE TABLE #TableList
(
rownum INT IDENTITY(1,1),
Table_name VARCHAR(100),
schema_name VARCHAR(100),
column_name VARCHAR(100)
)
--CHAR, VARCHAR, NCHAR, NVARCHAR ARE ONLY PICKED
Insert into #TableList
SELECT
t.name AS table_name,
SCHEMA_NAME(t.schema_id) AS schema_name,
c.name AS column_name
FROM
sys.tables AS t INNER JOIN sys.columns c
ON t.OBJECT_ID = c.OBJECT_ID
JOIN sys.types AS tp ON c.user_type_id=tp.user_type_id
WHERE
tp.name in ('char', 'varchar', 'nchar', 'nvarchar')
and c.name LIKE @ColumnToSearch
ORDER BY schema_name, table_name
Select @RowNum = Min(RowNum) from #TableList
WHILE @RowNum is not null
Begin
SELECT
@TableName = MIN(QUOTENAME(Table_Name)),
@ColumnName = MIN(QUOTENAME(Column_Name))
FROM #TableList
where RowNum = @RowNum
SELECT @RowNum = MIN(RowNum) FROM #TableList
WHERE RowNum > @RowNum
INSERT INTO #SearchResults
EXEC
(
'SELECT ''' + @TableName + ''', COUNT(' + @ColumnName + ')
FROM ' + @TableName + ' WITH (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @searchString
)
End
--RESULT IS SHOWN HERE
SELECT TableName, RecordCount FROM #SearchResults
--Cleanup
DROP TABLE #TableList
DROP TABLE #SearchResults
END
GO
To test:
Exec usp_SearchAllTables_InExistingDB 'CityID', '25'
Comments