February 25, 2009 at 7:27 am
Comments posted to this topic are about the item Search anything anywhere
April 1, 2009 at 7:49 am
Thanks Florian for a buggy script. That thing just fried my server. :angry:
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
April 1, 2009 at 8:05 am
Hi JacekO
What is the problem?
Greets
Flo
April 1, 2009 at 8:09 am
No problem, great script.
I could not resist the temptation and was trying to play an April's Fool joke on you. 😀
Thanks.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
April 1, 2009 at 8:12 am
Dang! You got me! 😀
I really feared that something might be damaged and started to review the script...!
Have a nice day 😉
Flo
April 1, 2009 at 9:53 am
It is a really nice script for SQL 2005 and SQL 2008 but it doesn't work in SQL 2000. Maybe that should be noted somewhere.
And I really like you Garfield. It is first thing in the morning here in AZ and he looks the way I feel!
April 1, 2009 at 10:49 am
This is a good script. I had a use for something similar some time ago and created the sproc below with some additional parameters to limit searches.
Create Procedure [dbo].[sp_ColumnValues]
@iTableNamevarchar(100),
@iColumnName varchar(100),
@iWherevarchar(100),
@idataTypevarchar(100),
@ivaluevarchar(100)
as
/*
Requires following inputs:
@iTableName - Table to be searched. Use wildcard '%' to search all tables.
@iColumnName - Column to be searched. Use wildcard '%' to search all Columns.
Unlikely to work when searching multiple tables.
@iWhere - Criteria to restrict results. Use the operator "in" to select multiple results.
Use an expression that is always true such as 1 = 1 to get all results.
This may result in a very long running query.
Unlikely to work when searching multiple tables.
@idataType - use this value to only search certain datatype ("int","char","decimal","smalldatetime")
Use wildcard '%' to search all datatypes.
@ivalue - value to be search for. Can use wildcards to broaden search.
Example
EXECUTE [MISDB].[dbo].[sp_ColumnValues]
@iTableName = 'accounts'
,@iColumnName = '%'
,@iWhere = 'acct_nbr = 99999999'
,@idataType = 'decimal'
,@ivalue = '''%.62'''
*/
If Len(@iTableName) = 0 set @iTableName = '%'
If Len(@iColumnName) = 0 set @iColumnName = '%'
IF OBJECT_ID (N'tempdb.dbo.#Tables',N'U' ) IS NOT NULL
Begin
Drop Table #Tables
End
Create Table #Tables(
TABLE_Qualifier varchar(100),
Table_Ownervarchar(100),
Table_Namevarchar(100),
Table_Typevarchar(100),
Remarksvarchar(100))
insert #Tables Exec sp_Tables
IF OBJECT_ID (N'tempdb.dbo.##Columns',N'U' ) IS NOT NULL
Begin
Drop Table ##Columns
End
Create Table ##Columns(
TABLE_QUALIFIERsysname,
TABLE_OWNERsysname,
TABLE_NAMEsysname,
COLUMN_NAMEsysname,
DATA_TYPEsmallint,
TYPE_NAMEsysname,
iPRECISIONint,
LENGTHint,
SCALEsmallint,
RADIXsmallint,
NULLABLEsmallint,
REMARKSvarchar(254),
COLUMN_DEFnvarchar(4000),
SQL_DATA_TYPEsmallint,
SQL_DATETIME_SUBsmallint,
CHAR_OCTET_LENGTHint,
ORDINAL_POSITIONint,
IS_NULLABLEvarchar(254),
SS_DATA_TYPEtinyint)
DECLARE @getTableID CURSOR
DECLARE @TableName varchar(100)
SET @getTableID = CURSOR FOR
SELECT Table_Name
FROM #Tables
Where Table_Type = 'TABLE' and Table_Name like @iTableName
OPEN @getTableID
FETCH NEXT FROM @getTableID INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
insert ##Columns Exec sp_Columns @table_name = @TableName
FETCH NEXT FROM @getTableID INTO @TableName
END
CLOSE @getTableID
DEALLOCATE @getTableID
DECLARE @SqlStatement nvarchar(4000)
IF OBJECT_ID (N'tempdb.dbo.##ColumnValues',N'U' ) IS NOT NULL
Begin
Drop Table ##ColumnValues
End
Create Table ##ColumnValues(
cValuevarchar(100),
ColumnNamevarchar(100))
DECLARE @getValueID CURSOR
DECLARE @vcolumn varchar(100)
Declare @vTablevarchar(100)
SET @getValueID = CURSOR FOR
SELECT Column_Name,Table_Name
from ##Columns
Where Column_Name like @iColumnName and Type_Name like @iDataType
OPEN @getValueID
FETCH NEXT FROM @getValueID INTO @vColumn,@vTable
WHILE @@FETCH_STATUS = 0
BEGIN
Select @SqlStatement =
'Insert Into ##ColumnValues
Select Convert(varchar(100),' + @vColumn + ') , ''' + @vColumn + ''' From ' + @vTable + ' Where ' + @iWhere
--Select @SqlStatement
EXEC sp_executesql @SqlStatement
FETCH NEXT FROM @getValueID INTO @vColumn,@vTable
END
CLOSE @getValueID
DEALLOCATE @getValueID
-- Return columns that match criteria
Select @SqlStatement = '
Select *
From ##ColumnValues
Where cvalue like ' + @ivalue
EXEC sp_executesql @SqlStatement
April 1, 2009 at 2:54 pm
Hi thermanson
So why don't you publish it here? 😉
My script was only a simple helper I wrote sometime. Since now several people within my company asked for it, so I thought there might be somebody else who may need it.
Greets
Flo
March 24, 2010 at 12:39 am
Hi,
I got some errors are prompted when I run this script. And I am using 2K8 SE
Msg 2715, Level 16, State 3, Line 81
Column, parameter, or variable #1: Cannot find data type SYSNAME.
Parameter or variable '@column' has an invalid data type.
Msg 2715, Level 16, State 3, Line 81
Column, parameter, or variable #3: Cannot find data type SYSNAME.
Parameter or variable '@schema' has an invalid data type.
Msg 2715, Level 16, State 3, Line 81
Column, parameter, or variable #4: Cannot find data type SYSNAME.
Parameter or variable '@table' has an invalid data type.
Please assist
March 24, 2010 at 2:17 am
Hi Madhu
Try to replace all "SYSNAME" data types with "NVARCHAR(128)".
Greets
Flo
June 5, 2013 at 7:54 am
I use this one (cant remember where I found it though):
CREATE TABLE myTable99 (TABLE_NAME sysname, COLUMN_NAME sysname, Occurs int)
GO
SET NOCOUNT ON
DECLARE @SQL varchar(8000), @TABLE_NAME sysname, @COLUMN_NAME sysname, @Sargable varchar(80), @Count int
SELECT @Sargable = 'enter word or words here'
DECLARE insaneCursor CURSOR FOR
SELECT c.TABLE_NAME, c.COLUMN_NAME
FROM INFORMATION_SCHEMA.Columns c INNER JOIN INFORMATION_SCHEMA.Tables t
ON t.TABLE_SCHEMA = c.TABLE_SCHEMA AND t.TABLE_NAME = c.TABLE_NAME
WHERE c.DATA_TYPE IN ('char','nchar','varchar','nvarchar','text','ntext')
AND t.TABLE_TYPE = 'BASE TABLE'
OPEN insaneCursor
FETCH NEXT FROM insaneCursor INTO @TABLE_NAME, @COLUMN_NAME
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @SQL = 'INSERT INTO myTable99 (TABLE_NAME, COLUMN_NAME, Occurs) SELECT '
+ '''' + @TABLE_NAME + '''' + ','
+ '''' + @COLUMN_NAME + '''' + ','
+ 'COUNT(*) FROM [' + @TABLE_NAME
+ '] WHERE [' + @COLUMN_NAME + '] Like '
+ ''''+ '%' + @Sargable + '%' + ''''
--SELECT @SQL
EXEC(@SQL)
IF @@ERROR <> 0
BEGIN
SELECT @SQL
SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = @TABLE_NAME
GOTO Error
END
FETCH NEXT FROM insaneCursor INTO @TABLE_NAME, @COLUMN_NAME
END
SELECT * FROM myTable99 WHERE Occurs <> 0
Error:
CLOSE insaneCursor
DEALLOCATE insaneCursor
GO
DROP TABLE myTable99
GO
SET NOCOUNT OFF
June 5, 2013 at 1:02 pm
Thanks for this!
I've been using a similar script, and I was wondering if it would be possible to include data in the same row as the data found? In other words, I have a 'date_created' column on most tables, and I would like to return that as well as the search result, in order to sort by date created.
Is this possible?
Thanks
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply