This is a simple stored procedure which can search through all the
data in the SQL Server database tables. Also this has capability to
search in the selected tables if the table names are specified with
comma separated values. This has a capability to generate the SQL
alone without executing the SQL. Enclosing the script version of it
also.
Parameters and usage:
@Tablenames -- Provide a single table name or multiple table name
with comma separated.
If left blank, it will check for all the tables in the database
@SearchStr -- Provide the search string. Use the '%' to coin the
search.
EX : X%--- will give data starting with X
%X--- will give data ending with X
%X%--- will give data containing X
@GenerateSQLOnly -- Provide 1 if you only want to generate the SQL
statements without searching the database.
By default it is 0 and it will search.
IF OBJECT_ID('SP_SearchTables','P') IS NOT NULL
DROP PROCEDURE SP_SearchTables
GO
CREATE PROCEDURE SP_SearchTables
@Tablenames VARCHAR(500)
,@SearchStr NVARCHAR(60)
,@GenerateSQLOnly Bit = 0
AS
SET NOCOUNT ON
DECLARE @MatchFound BIT
SELECT @MatchFound = 0
DECLARE @CheckTableNames Table
(
Tablename sysname
)
DECLARE @SQLTbl TABLE
(
Tablename SYSNAME
,WHEREClause VARCHAR(MAX)
,SQLStatement VARCHAR(MAX)
,Execstatus BIT
)
DECLARE @sql VARCHAR(MAX)
DECLARE @tmpTblname sysname
DECLARE @ErrMsg VARCHAR(100)
IF LTRIM(RTRIM(@Tablenames)) IN ('' ,'%')
BEGIN
INSERT INTO @CheckTableNames
SELECT Name
FROM sys.tables
END
ELSE
BEGIN
SELECT @sql = 'SELECT ''' + REPLACE(@Tablenames,',',''' UNION SELECT ''') + ''''
INSERT INTO @CheckTableNames
EXEC(@sql)
END
IF NOT EXISTS(SELECT 1 FROM @CheckTableNames)
BEGIN
SELECT @ErrMsg = 'No tables are found in this database ' + DB_NAME() + ' for the specified filter'
PRINT @ErrMsg
RETURN
END
INSERT INTO @SQLTbl
( Tablename,WHEREClause)
SELECT QUOTENAME(SCh.name) + '.' + QUOTENAME(ST.NAME),
(
SELECT '[' + SC.name + ']' + ' LIKE ''' + @SearchStr + ''' OR ' + CHAR(10)
FROM SYS.columns SC
JOIN SYS.types STy
ON STy.system_type_id = SC.system_type_id
AND STy.user_type_id =SC.user_type_id
WHERE STY.name in ('varchar','char','nvarchar','nchar')
AND SC.object_id = ST.object_id
ORDER BY SC.name
FOR XML PATH('')
)
FROM SYS.tables ST
JOIN @CheckTableNames chktbls
ON chktbls.Tablename = ST.name
JOIN SYS.schemas SCh
ON ST.schema_id = SCh.schema_id
WHERE ST.name <> 'SearchTMP'
GROUP BY ST.object_id, QUOTENAME(SCh.name) + '.' + QUOTENAME(ST.NAME) ;
UPDATE @SQLTbl
SET SQLStatement = 'SELECT * INTO SearchTMP FROM ' + Tablename + ' WHERE ' + substring(WHEREClause,1,len(WHEREClause)-5)
DELETE FROM @SQLTbl
WHERE WHEREClause IS NULL
WHILE EXISTS (SELECT 1 FROM @SQLTbl WHERE ISNULL(Execstatus ,0) = 0)
BEGIN
SELECT TOP 1 @tmpTblname = Tablename , @sql = SQLStatement
FROM @SQLTbl
WHERE ISNULL(Execstatus ,0) = 0
IF @GenerateSQLOnly = 0
BEGIN
IF OBJECT_ID('SearchTMP','U') IS NOT NULL
DROP TABLE SearchTMP
EXEC (@SQL)
IF EXISTS(SELECT 1 FROM SearchTMP)
BEGIN
SELECT Tablename=@tmpTblname,* FROM SearchTMP
SELECT @MatchFound = 1
END
END
ELSE
BEGIN
PRINT REPLICATE('-',100)
PRINT @tmpTblname
PRINT REPLICATE('-',100)
PRINT replace(@sql,'INTO SearchTMP','')
END
UPDATE @SQLTbl
SET Execstatus = 1
WHERE Tablename = @tmpTblname
END
IF @MatchFound = 0
BEGIN
SELECT @ErrMsg = 'No Matches are found in this database ' + DB_NAME() + ' for the specified filter'
PRINT @ErrMsg
RETURN
END
SET NOCOUNT OFF
go
By Sorna Kumar Muthuraj
Something I use at work really flexible useful piece of code.
If you don't have permissions to create procs just declare the variables being used in this script and execute script as ad-hoc query
DECLARE @Tablenames VARCHAR(500) = 'Table_Name'
DECLARE @SearchStr NVARCHAR(60) = 'Data_LookingFor'
DECLARE @GenerateSQLOnly Bit = 0
Your DBA really doesn't trust you does he :) Anyway, I have tweaked the code a little bit more making use of temp table rather than the Table variables, maybe this will work for you:
DECLARE @Tablenames VARCHAR(500) = 'Table_name'
DECLARE @SearchStr NVARCHAR(60) = 'Serach_String'
DECLARE @GenerateSQLOnly Bit = 0
SET NOCOUNT ON
DECLARE @MatchFound BIT
SELECT @MatchFound = 0
IF OBJECT_ID('tempdb..#CheckTableNames') IS NOT NULL
DROP TABLE #CheckTableNames
CREATE Table #CheckTableNames
(
Tablename sysname
)
IF OBJECT_ID('tempdb..#SQLTbl') IS NOT NULL
DROP TABLE #SQLTbl
CREATE TABLE #SQLTbl
(
Tablename SYSNAME
,WHEREClause VARCHAR(MAX)
,SQLStatement VARCHAR(MAX)
,Execstatus BIT
)
DECLARE @sql VARCHAR(MAX)
DECLARE @tmpTblname sysname
DECLARE @ErrMsg VARCHAR(100)
IF LTRIM(RTRIM(@Tablenames)) IN ('' ,'%')
BEGIN
INSERT INTO #CheckTableNames
SELECT Name
FROM sys.tables
END
ELSE
BEGIN
SELECT @sql = 'SELECT ''' + REPLACE(@Tablenames,',',''' UNION SELECT ''') + ''''
INSERT INTO #CheckTableNames
EXEC(@sql)
END
IF NOT EXISTS(SELECT 1 FROM #CheckTableNames)
BEGIN
SELECT @ErrMsg = 'No tables are found in this database ' + DB_NAME() + ' for the specified filter'
PRINT @ErrMsg
RETURN
END
INSERT INTO #SQLTbl
( Tablename,WHEREClause)
SELECT QUOTENAME(SCh.name) + '.' + QUOTENAME(ST.NAME),
(
SELECT '[' + SC.name + ']' + ' LIKE ''' + @SearchStr + ''' OR ' + CHAR(10)
FROM SYS.columns SC
JOIN SYS.types STy
ON STy.system_type_id = SC.system_type_id
AND STy.user_type_id =SC.user_type_id
WHERE STY.name in ('varchar','char','nvarchar','nchar')
AND SC.object_id = ST.object_id
ORDER BY SC.name
FOR XML PATH('')
)
FROM SYS.tables ST
JOIN #CheckTableNames chktbls
ON chktbls.Tablename = ST.name
JOIN SYS.schemas SCh
ON ST.schema_id = SCh.schema_id
WHERE ST.name <> 'SearchTMP'
GROUP BY ST.object_id, QUOTENAME(SCh.name) + '.' + QUOTENAME(ST.NAME) ;
UPDATE #SQLTbl
SET SQLStatement = 'SELECT * INTO SearchTMP FROM ' + Tablename + ' WHERE ' + substring(WHEREClause,1,len(WHEREClause)-5)
DELETE FROM #SQLTbl
WHERE WHEREClause IS NULL
WHILE EXISTS (SELECT 1 FROM #SQLTbl WHERE ISNULL(Execstatus ,0) = 0)
BEGIN
SELECT TOP 1 @tmpTblname = Tablename , @sql = SQLStatement
FROM #SQLTbl
WHERE ISNULL(Execstatus ,0) = 0
IF @GenerateSQLOnly = 0
BEGIN
IF OBJECT_ID('SearchTMP','U') IS NOT NULL
DROP TABLE SearchTMP
EXEC (@SQL)
IF EXISTS(SELECT 1 FROM SearchTMP)
BEGIN
SELECT Tablename = @tmpTblname,* FROM SearchTMP
SELECT @MatchFound = 1
END
END
ELSE
BEGIN
PRINT REPLICATE('-',100)
PRINT @tmpTblname
PRINT REPLICATE('-',100)
PRINT replace(@sql,'INTO SearchTMP','')
END
UPDATE #SQLTbl
SET Execstatus = 1
WHERE Tablename = @tmpTblname
END
IF @MatchFound = 0
BEGIN
SELECT @ErrMsg = 'No Matches are found in this database ' + DB_NAME() + ' for the specified filter'
PRINT @ErrMsg
RETURN
END
