Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Search anything anywhere Expand / Collapse
Author
Message
Posted Wednesday, February 25, 2009 7:27 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 1,893, Visits: 3,932
Comments posted to this topic are about the item Search anything anywhere


The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Post #664233
Posted Wednesday, April 1, 2009 7:49 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, March 29, 2011 2:59 PM
Points: 473, Visits: 606
Thanks Florian for a buggy script. That thing just fried my server.

---------------------------------------------
Nothing is impossible.
It is just a matter of time and money.
Post #687906
Posted Wednesday, April 1, 2009 8:05 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 1,893, Visits: 3,932
Hi JacekO

What is the problem?

Greets
Flo



The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Post #687929
Posted Wednesday, April 1, 2009 8:09 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, March 29, 2011 2:59 PM
Points: 473, Visits: 606
No problem, great script.
I could not resist the temptation and was trying to play an April's Fool joke on you.

Thanks.


---------------------------------------------
Nothing is impossible.
It is just a matter of time and money.
Post #687938
Posted Wednesday, April 1, 2009 8:12 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 1,893, Visits: 3,932
Dang! You got me!

I really feared that something might be damaged and started to review the script...!

Have a nice day
Flo



The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Post #687943
Posted Wednesday, April 1, 2009 9:53 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 5:35 PM
Points: 205, Visits: 59
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!
Post #688122
Posted Wednesday, April 1, 2009 10:49 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, January 7, 2014 1:16 PM
Points: 43, Visits: 287
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]
@iTableName varchar(100),
@iColumnName varchar(100),
@iWhere varchar(100),
@idataType varchar(100),
@ivalue varchar(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_Owner varchar(100),
Table_Name varchar(100),
Table_Type varchar(100),
Remarks varchar(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_QUALIFIER sysname,
TABLE_OWNER sysname,
TABLE_NAME sysname,
COLUMN_NAME sysname,
DATA_TYPE smallint,
TYPE_NAME sysname,
iPRECISION int,
LENGTH int,
SCALE smallint,
RADIX smallint,
NULLABLE smallint,
REMARKS varchar(254),
COLUMN_DEF nvarchar(4000),
SQL_DATA_TYPE smallint,
SQL_DATETIME_SUB smallint,
CHAR_OCTET_LENGTH int,
ORDINAL_POSITION int,
IS_NULLABLE varchar(254),
SS_DATA_TYPE tinyint)



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(
cValue varchar(100),
ColumnName varchar(100))

DECLARE @getValueID CURSOR
DECLARE @vcolumn varchar(100)
Declare @vTable varchar(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

Post #688178
Posted Wednesday, April 1, 2009 2:54 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 1,893, Visits: 3,932
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



The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Post #688391
Posted Wednesday, March 24, 2010 12:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, March 24, 2013 6:56 PM
Points: 6, Visits: 42
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
Post #888711
Posted Wednesday, March 24, 2010 2:17 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 1,893, Visits: 3,932
Hi Madhu

Try to replace all "SYSNAME" data types with "NVARCHAR(128)".

Greets
Flo



The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Post #888736
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse