March 14, 2013 at 7:36 am
You have no idea how many times in the six months I've been at this job that I've wished for a script like this. Thank you so much!
ron
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown
March 14, 2013 at 8:00 am
This is great. There have definitely been times in the past that this would come in handy, and I'm sure I'll use it again in the future. It took around 20 minutes to run, but that easily beats out sifting through several tables I'm not familiar with. 🙂
March 14, 2013 at 8:13 am
Geesh...you guys beat me to it!
if exists
(
select OBJECT_NAME(o.id) as tempdbname, * from tempdb.dbo.sysobjects o
where o.xtype in ('U')
and o.id = object_id(N'tempdb..#Table_Name')
)
DROP TABLE #Table_Name;
if exists
(
select OBJECT_NAME(o.id) as tempdbname, * from tempdb.dbo.sysobjects o
where o.xtype in ('U')
and o.id = object_id(N'tempdb..#SearchTestResult')
)
DROP TABLE #SearchTestResult;
if exists
(
select OBJECT_NAME(o.id) as tempdbname, * from tempdb.dbo.sysobjects o
where o.xtype in ('U')
and o.id = object_id(N'tempdb..#SearchResult')
)
DROP TABLE #SearchResult;
March 14, 2013 at 8:14 am
Below one is more efficient and gives more info as well ... I have used it many times and its a life saver
Credit goes to -- Luis Chiriff
/*
- Search through tables to find specific text
- Written by Luis Chiriff (with help from SQL Server Central)
- luis.chiriff@gmail.com @ 24/11/2008 @ 11:54
*/
-- Variable Declaration
Declare @StringToFind VARCHAR(200), @Schema sysname, @Table sysname, @FullTable int, @NewMinID int, @NewMaxID int,
@SQLCommand VARCHAR(8000), @BaseSQLCommand varchar(8000), @Where VARCHAR(8000), @CountCheck varchar(8000) , @FieldTypes varchar(8000),
@cursor VARCHAR(8000), @columnName sysname, @SCn int, @SCm int
Declare @TableList table (Id int identity(1,1) not null, tablename varchar(250))
Declare @SQLCmds table (id int identity(1,1) not null, sqlcmd varchar(8000))
Declare @DataFoundInTables table (id int identity(1,1) not null, sqlcmd varchar(8000))
-- Settings
SET @StringToFind = 'territory'
SET NOCOUNT ON
SET @StringToFind = '%'+@StringToFind+'%'
-- Gathering Info
if ((select count(*) from sysobjects where name = 'tempcount') > 0)
drop table tempcount
create table tempcount (rowsfound int)
insert into tempcount select 0
-- This section here is to accomodate the user defined datatypes, if they have
-- a SQL Collation then they are assumed to have text in them.
SET @FieldTypes = ''
select @FieldTypes = @FieldTypes + '''' + rtrim(ltrim(name))+''',' from systypes where collation is not null or xtype = 36
select @FieldTypes = left(@FieldTypes,(len(@FieldTypes)-1))
insert into @TableList (tablename)
select name from sysobjects
where xtype = 'U' and name not like 'dtproperties'
order by name
-- Start Processing Table List
select @NewMinID = min(id), @NewMaxID = max(id) from @TableList
while(@NewMinID <= @NewMaxID)
Begin
SELECT @Table = tablename, @Schema='dbo', @Where = '' from @TableList where id = @NewMinID
SET @SQLCommand = 'SELECT * FROM ' + @Table + ' WHERE'
-- removed ' + @Schema + '.
SET @cursor = 'DECLARE col_cursor CURSOR FOR SELECT COLUMN_NAME
FROM [' + DB_NAME() + '].INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = ''' + @Schema + '''
AND TABLE_NAME = ''' + @Table + '''
AND DATA_TYPE IN ('+@FieldTypes+')'
--Original Check, however the above implements user defined data types --AND DATA_TYPE IN (''char'',''nchar'',''ntext'',''nvarchar'',''text'',''varchar'')'
EXEC (@cursor)
SET @FullTable = 0
DELETE FROM @SQLCmds
OPEN col_cursor
FETCH NEXT FROM col_cursor INTO @columnName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Where = @Where + ' [' + @columnName + '] LIKE ''' + @StringToFind + ''''
SET @Where = @Where + ' OR'
--PRINT @Table + '|'+ cast(len(isnull(@Where,''))+len(isnull(@SQLCommand,'')) as varchar(10))+'|'+@Where
if (len(isnull(@Where,''))+len(isnull(@SQLCommand,'')) > 3600)
Begin
SELECT @Where = substring(@Where,1,len(@Where)-3)
insert into @SQLCmds (sqlcmd) select @Where
SET @Where = ''
End
FETCH NEXT FROM col_cursor INTO @columnName
END
CLOSE col_cursor
DEALLOCATE col_cursor
if (@Where <> '')
Begin
SELECT @Where = substring(@Where,1,len(@Where)-3)
insert into @SQLCmds (sqlcmd)
select @Where --select @Table,count(*) from @SQLCmds
End
SET @BaseSQLCommand = @SQLCommand
select @SCn = min(id), @SCm = max(id) from @SQLCmds
while(@SCn <= @SCm)
Begin
select @Where = sqlcmd from @SQLCmds where ID = @SCn
if (@Where <> '')
Begin
SET @SQLCommand = @BaseSQLCommand + @Where
SELECT @CountCheck = 'update tempcount set rowsfound = (select count(*) '+ substring(@SQLCommand,10,len(@SQLCommand)) + ')'
EXEC (@CountCheck)
if ((select rowsfound from tempcount) > 0)
Begin
PRINT '--- ['+cast(@NewMinID as varchar(15))+'/'+cast(@NewMaxID as varchar(15))+'] '+@Table + ' ----------------------------------[FOUND!]'
--PRINT '--- [FOUND USING:] ' +@SQLCommand
insert into @DataFoundInTables (sqlcmd) select @SQLCommand
EXEC (@SQLCommand)
update tempcount set rowsfound = 0
End
else
Begin
PRINT '--- ['+cast(@NewMinID as varchar(15))+'/'+cast(@NewMaxID as varchar(15))+'] '+@Table
End
End
SET @SCn = @SCn + 1
End
set @NewMinID = @NewMinID + 1
end
if ((select count(*) from sysobjects where name = 'tempcount') > 0)
drop table tempcount
/*
This will now return all the sql commands you need to use
*/
select @NewMinID = min(id), @NewMaxID = max(id) from @DataFoundInTables
if (@NewMaxID > 0)
Begin
PRINT ' '
PRINT ' '
PRINT '-----------------------------------------'
PRINT '----------- TABLES WITH DATA ------------'
PRINT '-----------------------------------------'
PRINT ' '
PRINT 'We found ' + cast(@NewMaxID as varchar(10)) + ' table(s) with the string '+@StringToFind
PRINT ' '
while(@NewMinID <= @NewMaxID)
Begin
select @SQLCommand = sqlcmd from @DataFoundInTables where ID = @NewMinID
PRINT @SQLCommand
SET @NewMinID = @NewMinID + 1
End
PRINT ' '
PRINT '-----------------------------------------'
End
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
March 14, 2013 at 9:38 am
SQLQuest29
that is prety neat script. i am adding that to my tool box.
note to other users - send result to text - (CTRL +t)
thanks!
March 14, 2013 at 10:09 am
Just adding my solution,
I've been using this one for several years now.
CREATE PROCEDURE [dbo].[Dev_FindString](@S nvarchar(50))
AS
BEGIN
/*
Search through colums for specified text (@S)
Written: Louis Hillebrand
*/
SET NOCOUNT ON
DECLARE@Id int = 1,
@Cnt int,
@sql nvarchar(4000),
@ParmDefinition nvarchar(100) = '@S nvarchar(50), @Cnt int OUTPUT';
DECLARE @USR_FIND TABLE ([Id] int identity(1,1), [Schema] sysname, [Table] sysname, [Column] sysname, [Count] int);
INSERT @USR_FIND ([Schema], [Table], [Column], [Count])
SELECTA.TABLE_SCHEMA,
A.TABLE_NAME,
A.COLUMN_NAME,
0
FROMINFORMATION_SCHEMA.Columns A INNER JOIN
INFORMATION_SCHEMA.Tables B ON A.TABLE_NAME = B.TABLE_NAME
WHEREB.TABLE_TYPE = 'BASE TABLE' AND NOT B.Table_Name LIKE '%MSMERGE%'
ANDA.Data_Type IN( 'nvarchar' , 'nchar', 'nchar', 'char');
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHILE EXISTS(SELECT 1 FROM @USR_FIND WHERE Id = @Id)
BEGIN;
SELECT@sql = 'SELECT @Cnt = COUNT(*) FROM [' + [Schema] + '].[' + [Table] + '] WHERE [' + [Column] + '] LIKE @s-2 '
FROM@USR_Find WHERE Id = @Id
EXEC sp_ExecuteSQL @sql, @ParmDefinition, @s-2 = @s-2, @Cnt = @Cnt OUTPUT;
IF @Cnt > 0
UPDATE @USR_FIND
SETCount = @Cnt
WHEREId = @Id
SET@Id +=1
END;
SELECT [Schema], [Table], [Column], [Count] FROM @USR_FIND WHERE Count > 0 ORDER BY 1, 2, 3;
END
GO
EXEC Dev_FindString 'Louis'
March 14, 2013 at 10:41 am
I guess if I am learning about a new database that I have been given to support and am searching like this in a development environment, I can see how this would help.
I think I would have to kill anyone that did such a thing in our production database.
If I understand, this will run an open-ended LIKE on every table (edit: on every column of every table?)
Depending on the table... YIKES.
March 14, 2013 at 10:57 am
scott mcnitt (3/14/2013)
I guess if I am learning about a new database that I have been given to support and am searching like this in a development environment, I can see how this would help.I think I would have to kill anyone that did such a thing in our production database.
If I understand, this will run an open-ended LIKE on every table (edit: on every column of every table?)
Depending on the table... YIKES.
Agreed with you to some extent. There are couple of situations where this would be a life saver and running in PROD makes sense as well ...
a. Imagine a situation where the front end application is crashing because of "bad data" inserted by user using -- copy paste or the sql provider does not translate it properly and thereby inserting "garbage" into database.
b. There might be a situation, after cross RDBMS migration like Sybase/Oracle to SQL Server and after the migration there might be data truncation or some other problem with the data.
You would argue that these must be tested in UAT/QC/DEV, etc ,but trust me ... mistakes/disasters do happen and this will be a life saver ... 😀
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
March 14, 2013 at 11:10 am
Quest, I get it and do not want to seem like my tone is negative here.
I can only hope that I am never asked to "find some bad data that looks something like
*this* somewhere in our prod database." Nobody knows the schema. Nobody can answer mapping questions.
I bet Nobody will complain when the application becomes unusable for 20 minutes with timeouts and they find out that it was a search I was doing.
I like all the input with variations. They are all good tools to have in your belt.
March 14, 2013 at 11:56 am
I would add a criteria to only search the columns that could conceivably contain the data. There's no use searching numeric columns for character data, and if you are looking for 5 characters, there's no need to search any character type columns that are shorter.
I did something like this looking for all occurrences of bad currency codes, and ignored all of the columns that were less than 3 characters wide or were numebric, boolean, datetime, etc. I also used count(*) to give me a summary of how many rows were affected, something like:
set @sql = 'select ''' +@tn + ''', ''' + @cn + ''', count(*) from ' + @TN + ' where '
+ @cn + ' like ''%xxxx%'' having count(*) <> 0'
where @TN and @cn are generated by this cursor query:
declare tc cursor for
select b.table_name, a.column_name from information_schema.columns a
join information_schema.tables b
on a.table_name = b.table_name
where ((a.data_type = 'varchar' or a.data_type = 'char')and a.character_maximum_length > 2)
and b.table_type = 'base table'
order by b.table_name, a.ordinal_position
March 14, 2013 at 3:15 pm
OMG- Tinkered with this and have it pulling where defined variable indicates a social security number.
Basically replacing the data string with '[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]'
I working on another change to find all tables, the db I am looking at uses wack out owner id.
Thanks!!!!!
BTW- we are building utility for security to run this periodically after selecting db and defining variable
March 15, 2013 at 6:51 am
I forgot to add to my earlier post that adding an order by table name and ordinal position in the information_schema select that drives the cursor made a huge difference in performance. For my data, the time to check the entire database went from 8 minutes to under 1.
March 16, 2013 at 11:21 am
I don't understand why a script to scan across all tables in a database would be needed. Generally speaking, we know ahead of time if we're looking for a customer phone number, order number, etc. In a database with a normalized design and proper naming conventions, it should be obvious where to look.
Should we find ourselves in the unfortunate position of being handed a database of 200 tables with names like 'MSGCATDEFN' or 'C9', then it may be easier and more rewarding to just polish up the resume and move on to another job.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
March 18, 2013 at 8:14 am
In addition to the earlier suggestions made, I played around with searching other data types as well:
Declare @keytosearch varchar(max), @numtosearch varchar(max), @datetosearch varchar(25), @Database_Selected varchar(50)
set @keytosearch = null
set @numtosearch = '25.00'
set @datetosearch = '1967-01-20'
Then I duplicated the IF block for each data type. For numerical data types:
if @numtosearch is not null and @data_type is not null and (@data_type like '%int%' or @data_type='single'
or @data_type='double' or @data_type = 'decimal' or @data_type = 'money' or @data_type = 'numeric'
or @data_type = 'float')
begin
set @Result=null
if @column like '%-%' begin set @Result =('SELECT ''' + @column +''' FROM ' + @Table
+' Where ''' + @column + ''' = '+@numtosearch+'') end
else
set @Result =('SELECT ' + @column +' FROM ' + @Table +' Where ' + @column + ' = '+@numtosearch+'')
insert into #SearchTestResult exec(@Result)
set @ID=0
set @ID=(Select ID from #SearchTestResult where ID=Scope_identity())
if @ID is not null
begin
set @ID_inserted=(select top 1 ID from #SearchResult order by ID desc)
if @ID_inserted = @ID
begin
print ''
end
else
insert into #SearchResult values (@Table,@column)
end
end
and for dates:
if @datetosearch is not null and @data_type is not null and (@data_type like '%date%')
begin
set @Result=null
if @column like '%-%' begin set @Result =('SELECT ''' + @column +''' FROM ' + @Table
+' Where ''' + @column + ''' = '''+@datetosearch+'''') end
else
set @Result =('SELECT ' + @column +' FROM ' + @Table +' Where ' + @column + ' = '''+@datetosearch+'''')
insert into #SearchTestResult exec(@Result)
set @ID=0
set @ID=(Select ID from #SearchTestResult where ID=Scope_identity())
if @ID is not null
begin
set @ID_inserted=(select top 1 ID from #SearchResult order by ID desc)
if @ID_inserted = @ID
begin
print ''
end
else
insert into #SearchResult values (@Table,@column)
end
end
I also changed the varchar check to also look for char and nchar types by using @data_type like '%char' in the IF predicate.
March 18, 2013 at 11:10 am
Eric M Russell (3/16/2013)
I don't understand why a script to scan across all tables in a database would be needed. Generally speaking, we know ahead of time if we're looking for a customer phone number, order number, etc. In a database with a normalized design and proper naming conventions, it should be obvious where to look.
In a perfect world, that would be true. Unfortunately, many of us work with systems that are rewrites of rebuilds of something that was converted from a mainframe hierarchical design 15 years ago.
In my case, I came up with this to identify all the tables and fields that contained currency codes that were not ISO compliant, for instance using US$ instead of the ISO standard USD. This was driven by development of an interface to external parties that require ISO standard codes. It's not the sort of thing you would normally use often.
As far as polishing a resume, after 12 years with the company, and 3 years from earning full retirement with medical, I am not likely to change jobs just because I've been given something difficult to do.
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply