searching multiple tables for a piece of data

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)

searching multiple tables for a piece of data

Author

Topic

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2012-11-23 : 07:29:38

I have hundreads of tables that I need to search for one piece of data. Is there an easy was to search threw all columns in all tables for something like a code "p23lm"

Dave
Helixpoint Web Development
http://www.helixpoint.com

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-11-23 : 07:44:27

[code]
CREATE PROC SearchAllTables
(
@SearchStr nvarchar(100)
)
AS
BEGIN
DECLARE @Results TABLE(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'
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
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 ('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
END

SELECT ColumnName, ColumnValue FROM @Results
END
[/code]
I did surfing for this Procedure. You can use it for your requirement

and also see this link for more information:
http://weblogs.sqlteam.com/brettk/archive/2005/02/08/4144.aspx
--
Chandu

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2012-11-23 : 09:11:18

Very Cool!!!! Thanx

Dave
Helixpoint Web Development
http://www.helixpoint.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources