Help with the SP. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Help with the SP.

Author

Topic

NguyenL71
Posting Yak Master

228 Posts

Posted - 2010-08-09 : 14:34:59

I am try to search for a date like 01/01/1900 but it does not. I found this sp on web site and wonder if you can help debugging
the probblem. I try to run the sp to return all the columns and tables that have 01/01/1900 values in the tables.
--SQL2008

Thank you.

IF OBJECT_ID('dbo.usp_SearchAllTables', 'p') IS NOT NULL
DROP PROCedure dbo.usp_SearchAllTables
GO
CREATE PROCedure dbo.usp_SearchAllTables
(
@SearchStr VARCHAR(100)
)
AS
BEGIN

-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string
-- Written by: Narayana Vyas Kondreddi
-- Site: http://vyaskn.tripod.com
-- Tested on: SQL Server 7.0 and SQL Server 2000
-- Date modified: 28th July 2002 22:50 GMT

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, '''')

SELECT @SearchStr2

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'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
PRINT 'inside'
SELECT @TableName, @ColumnName
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('datetime', 'char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(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
--SELECT @SearchStr2
--SELECT @TableName
END

SELECT DISTINCT
CAST(ColumnName AS VARCHAR(50)) AS 'Table..ColName',
CAST(ColumnValue AS VARCHAR(50)) AS 'ColValues'
FROM #Results
END
go

------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID('TestTable ', 'u') IS NOT NULL
DROP TABLE TestTable
GO
CREATE TABLE TestTable
(
InsertDt DATETIME NULL,
ActiveAgent CHAR(3) NULL,
ActiveVendor CHAR(1) NULL
)
GO

INSERT INTO TestTable VALUES ('01/01/1900', 'Yes', 'Y'), ('02/16/2008', 'Yes', 'N'), ('01/01/1900', 'No', 'N'),
('06/15/2010', 'Yes', 'N'), ('01/01/1900', 'Yes', 'Y');

go

IF OBJECT_ID('Cust ', 'u') IS NOT NULL
DROP TABLE Cust
GO
CREATE TABLE Cust
(
InsertDt DATETIME NULL,
ActiveAgent CHAR(3) NULL,
ActiveVendor CHAR(1) NULL
)
GO

INSERT INTO Cust VALUES ('01/01/1900', 'Yes', 'Y'), ('02/16/2008', 'Yes', 'N'), ('01/01/1900', 'No', 'N'),
('06/15/2010', 'NO', 'N'), ('01/01/1900', 'Yes', 'Y');

go

SELECT *
FROM TestTable;
GO

SELECT *
FROM Cust;
GO

----------------------------------------------------------------------------------------------------

EXECute dbo.usp_SearchAllTables @SearchStr = '1900-01-01';
go

/*
InsertDt ActiveAgent ActiveVendor
----------------------- ----------- ------------
1900-01-01 00:00:00.000 Yes Y
2008-02-16 00:00:00.000 Yes N
1900-01-01 00:00:00.000 No N
2010-06-15 00:00:00.000 Yes N
1900-01-01 00:00:00.000 Yes Y

*/

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-08-09 : 15:52:51

[code]
set nocount on
declare @sql varchar(max)
select @sql=isnull(@sql,'') + replace(replace('if exists(select * from [ ! ] where [ ? ] = '''+@searchstr +''') print '' ! . ? ''; ',
' ! ', table_name), ' ? ', column_name)
from information_schema.columns where data_type IN ('char','nchar','nvarchar' , 'datetime')

EXEC(@sql)
[/code]

I find it's usually faster to just copy and paste the columns from each table in excel, write the query in Excel with the select * from table
where col1 like '%'+ '@SearchStr + '%'
or col2 like '%'+ '@SearchStr + '%'

and run it using brute force

Jim

Everyday I learn something that somebody else already knew

NguyenL71
Posting Yak Master

228 Posts

Posted - 2010-08-09 : 16:36:41

Thank you Jim.

quote:
Originally posted by jimf

set nocount on
declare @sql varchar(max)
select @sql=isnull(@sql,'') + replace(replace('if exists(select * from [ ! ] where [ ? ] = '''+@searchstr +''') print '' ! . ? ''; ',
' ! ', table_name), ' ? ', column_name)
from information_schema.columns where data_type IN ('char','nchar','nvarchar' , 'datetime')

EXEC(@sql)

I find it's usually faster to just copy and paste the columns from each table in excel, write the query in Excel with the select * from table
where col1 like '%'+ '@SearchStr + '%'
or col2 like '%'+ '@SearchStr + '%'

and run it using brute force

Jim

Everyday I learn something that somebody else already knew

NguyenL71
Posting Yak Master

228 Posts

Posted - 2010-08-09 : 16:58:57

Jim,

I am getting errors when executing the script. I know it has to do with 8000 chars limitation.

-- Return all the columns which has 01/01/1900 values.
SET NOCOUNT ON
DECLARE @sql varchar(8000),
@crlf VARCHAR(2),
@searchstr VARCHAR(50)
SET @crlf = CHAR(10) + CHAR(13)
SET @searchstr = '19000101'

SELECT @sql = ISNULL (@sql,'') + replace(replace('IF EXISTS (SELECT 1 FROM [ ! ] WHERE [ ? ] = ''' + @searchstr + ''') PRINT '' ! . ? ''; ',' ! ', table_name), ' ? ', column_name) + @crlf
FROM information_schema.columns
WHERE (data_type IN ('VARCHAR', 'char', 'nchar', 'nvarchar' , 'datetime') )

SELECT @sql

EXECute (@sql);

--Errors.
IF EXISTS (SELECT 1 FROM [VTOFAC] WHERE [OFACNote] = '19000101') PRINT 'VTOFAC.OFACNote'; ..... IF EXISTS

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'EXISTS'.

quote:
Originally posted by jimf

set nocount on
declare @sql varchar(max)
select @sql=isnull(@sql,'') + replace(replace('if exists(select * from [ ! ] where [ ? ] = '''+@searchstr +''') print '' ! . ? ''; ',
' ! ', table_name), ' ? ', column_name)
from information_schema.columns where data_type IN ('char','nchar','nvarchar' , 'datetime')

EXEC(@sql)

I find it's usually faster to just copy and paste the columns from each table in excel, write the query in Excel with the select * from table
where col1 like '%'+ '@SearchStr + '%'
or col2 like '%'+ '@SearchStr + '%'

and run it using brute force

Jim

Everyday I learn something that somebody else already knew

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-08-09 : 20:17:16

If you're on SQL 2005 or above, then use varchar(max) instead of varchar(8000), but I think this should run

IF EXISTS (SELECT 1 FROM [VTOFAC] WHERE [OFACNote] = '19000101') PRINT 'VTOFAC.OFACNote'

Jim

Everyday I learn something that somebody else already knew

NguyenL71
Posting Yak Master

228 Posts

Posted - 2010-08-10 : 11:15:36

I change to Max then it works. Thanks so much.

L

quote:
Originally posted by jimf

If you're on SQL 2005 or above, then use varchar(max) instead of varchar(8000), but I think this should run

IF EXISTS (SELECT 1 FROM [VTOFAC] WHERE [OFACNote] = '19000101') PRINT 'VTOFAC.OFACNote'

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources