March 13, 2013 at 11:08 pm
Comments posted to this topic are about the item Search a value to complete database
March 14, 2013 at 12:55 am
Hi Gaurav,
very helpfull query, thank you.
Can i suggest to add checks if the temporary tables already exists before creating them?
Something like:
IF OBJECT_ID('tempdb..#SearchResult') IS NOT NULL DROP TABLE #SearchResult
IF OBJECT_ID('tempdb..#SearchTestResult') IS NOT NULL DROP TABLE #SearchTestResult
IF OBJECT_ID('tempdb..#Table_Name') IS NOT NULL DROP TABLE #Table_Name
Thanks again,
Martin
March 14, 2013 at 1:35 am
Hello,
Thanks for the comment Martin.
Yes in case you are using it as a query then these checks need to be applied but if you are using as a proc(as I was using) then there is no need it will slow down the process.
Thanks
Gaurav Goel
March 14, 2013 at 1:49 am
Hi Gaurav,
another suggestion is that you add a variable for schema as I see you assume that all tables will be in a "dbo" schema
March 14, 2013 at 1:49 am
This doesn't search in the complete database.
It walks through the .dbo schema only. If the tables are in another schema, the script fails.
Shouldn't you use
Declare @Schema_Selected varchar(50)
set @Schema_Selected= 'whatever'
with ['+@Schema+']' instead of the hard-coded schemaname?
or even beter, retrieve the schemaname. Why do you use a cursor? In this cases I use a WHILE loop.
I work since SQL2000 with this script:
CREATE PROC [dbo].[SearchAllTables]
(
@SearchStr nvarchar(100)
)
AS
BEGIN
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
SET NOCOUNT ON
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ANDQUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
ANDOBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)
WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA= PARSENAME(@TableName, 2)
ANDTABLE_NAME= PARSENAME(@TableName, 1)
ANDDATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar','int')
ANDQUOTENAME(COLUMN_NAME) > @ColumnName
)
IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END
SELECT ColumnName, ColumnValue FROM #Results
END
March 14, 2013 at 1:56 am
Also to make provisions for Databases,schemas,Tables and fields names that have spaces by adding "[" and "]" to the strings
March 14, 2013 at 2:09 am
tyboutmarc (3/14/2013)
This doesn't search in the complete database.It walks through the .dbo schema only. If the tables are in another schema, the script fails.
Shouldn't you use
Declare @Schema_Selected varchar(50)
set @Schema_Selected= 'whatever'
with ['+@Schema+']' instead of the hard-coded schemaname?
or even beter, retrieve the schemaname. Why do you use a cursor? In this cases I use a WHILE loop.
I work since SQL2000 with this script:
CREATE PROC [dbo].[SearchAllTables]
(
@SearchStr nvarchar(100)
)
AS
BEGIN
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
SET NOCOUNT ON
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ANDQUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
ANDOBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)
WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA= PARSENAME(@TableName, 2)
ANDTABLE_NAME= PARSENAME(@TableName, 1)
ANDDATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar','int')
ANDQUOTENAME(COLUMN_NAME) > @ColumnName
)
IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END
SELECT ColumnName, ColumnValue FROM #Results
END
Nice one.
March 14, 2013 at 2:28 am
Here you go I updated your code to reflect some of the suggestions
set nocount on
--Cleanup
if exists (select * from tempdb.sys.tables where name like '#SearchResult%') drop table #SearchResult
if exists (select * from tempdb.sys.tables where name like '#SearchTestResult%') drop table #SearchTestResult
if exists (select * from tempdb.sys.tables where name like '#Table_Name%') drop table #Table_Name
--Variable Delaration
Declare @keytosearch varchar(max), @Database_Selected varchar(50)
set @keytosearch ='%Toufeeq%'
set @Database_Selected= 'MIS-Stage-Production-Analysis'
Declare @Table varchar(100), @Table_Name Cursor, @Count_Column int, @Result nvarchar(4000),@ID int,@ID_inserted int,@Count_Table int, @data_type varchar(10)
set @ID_inserted=0
set @Count_Table=0
DECLARE @schema varchar(250), @column varchar(max), @Column_Name CURSOR
--Variable Delaration end
--Second Cursor start
declare @informationName varchar(250), @SysName varchar(50), @Var varchar(5)
set @informationName='[' + @Database_Selected+'].'+'information_schema.COLUMNS'
Set @SysName='[' +@Database_Selected+'].'+'sys.objects'
Set @Var='u'
--Database Selected start
Create Table #SearchResult(ID int identity(1,1) not null, TableName varchar(50), ColumnName varchar(50))
Create Table #SearchTestResult(ID int identity(1,1) not null, ColumnName varchar(max))
Create Table #Table_Name(ID int identity(1,1) not null, table_name varchar(50))
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME='Column_Nam')
drop table Column_Nam
insert into #Table_Name exec ('Select name from '+@SysName +' where type= '''+@Var+'''')
--First Cursor open
SET @Table_Name = CURSOR FOR Select table_name from #Table_Name
open @Table_Name
Fetch Next from @Table_Name into @Table
WHILE @@FETCH_STATUS = 0
BEGIN
set @Count_Table =@Count_Table+1
--Second cursor opened
--print 'Select column_name from '+@informationName +' where table_name= '''+@Table+''''s
--Print 'SELECT table_schema,table_Name,column_name,data_type INTO Column_Nam FROM '+ @informationName +' where table_name = '''+@Table+''''
Exec('SELECT table_schema,table_Name,column_name,data_type INTO Column_Nam FROM '+ @informationName +' where table_name = '''+@Table+'''')
SET @Column_Name = CURSOR FOR (select table_schema,column_name from Column_Nam )
OPEN @Column_Name FETCH NEXT FROM @Column_Name INTO @schema,@column
set @Table='[' +@Database_Selected+'].['+ @schema +'].['+@Table+']'
WHILE @@FETCH_STATUS = 0
BEGIN
set @data_type= (SELECT top 1 data_type FROM Column_Nam Where column_name= @column)
if @data_type is not null and (@data_type='varchar' or @data_type='nvarchar' or @data_type='Text')
begin
set @Result=null
/*if @column like '%-%' begin
set @Result =('SELECT ''' + @column +''' FROM ' + @Table
+' Where ''' + @column + ''' Like '''+@keytosearch+'''') end
else*/
set @Result =('SELECT [' + @column +'] FROM ' + @Table
+' Where [' + @column + '] Like '''+@keytosearch+'''')
Print @Result
insert into #SearchTestResult exec(@Result)
set @ID=0
set @ID=(Select ID from #SearchTestResult where ID= @@IDENTITY /*Scope_identity()*/ )
if @ID is not null
begin
select @ID_inserted=(select top 1 ID from #SearchResult order by ID desc)
if coalesce(@ID_inserted,0) <> @ID
insert into #SearchResult values (@Table,@column)
end
end
FETCH NEXT FROM @Column_Name INTO @schema,@column
END
CLOSE @Column_Name
DEALLOCATE @Column_Name
--Second cursor closed
drop table Column_Nam
Fetch Next from @Table_Name into @Table
End
close @Table_Name
Deallocate @Table_Name
--First Cursor Closed
Select * from #SearchResult
March 14, 2013 at 3:53 am
Hello All,
Thanks for your comments and suggestions.
Also thanks Grasshopper for modifying the code.
I made a basic version for searching throughout the database and it can be extended as per your needs.
Thanks
Gaurav Goel
March 14, 2013 at 5:18 am
Love this!!! :w00t:
Could it be modified to search for patterns? Like trying to find Social Security Numbers as long as they were formatted
where pattern= 'xxx-xx-xxxx'
That would be the ultimate add at least to me.
I will work on doing this, however, my level is no way to forum's.
March 14, 2013 at 5:35 am
Yes douglas.bentley, you can modify it according to your requirement. There are some wild card characters in sql server which can help you out with this.
March 14, 2013 at 6:21 am
The original query did not work on CA Clarity database, but produced hundreds of errors.
The modified query works like a charm.
Thanks
Martti K.
March 14, 2013 at 6:44 am
Perhaps I am missing something, but aren't you performing a whole lot more work than is necessary? When you execute the following dynamic SQL:
set @Result =('SELECT ' + @column +' FROM ' + @Table +' Where ' + @column + ' Like '''+@keytosearch+'''')
… aren’t you actually storing the column value of every record in the entire table that matches your search criteria? If all you really want to know are the unique tables/columns that contain the value, I would think it much more efficient to just select the top(1) record or use an “exists” statement, that way as soon as the first matching record is found it exits the cycle rather than continuing to scan every record in the entire table, which could be very expensive when doing a “like” comparison on a large table.
March 14, 2013 at 7:10 am
Could it be modified to search for patterns? Like trying to find Social Security Numbers as long as they were formatted
where pattern= 'xxx-xx-xxxx'
SQL has very limited support for patterns and no regular expression support built in. Check out this SO question.
March 14, 2013 at 7:35 am
Ok... so even stepping away from the question that is begging to be asked of why...
Would it not be better to add a bunch of 'or' additions, so that when you end up scanning all 200 unindexed columns on the big table you only have to scan it once?
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply