Then run:
exec searchalltables 'rld7401'
Replace rld7401 with the data you are looking for.
Jeff D. Metcalf
IT Director
Cplus Sales & Service
864 801 9003
Never doubt that a small group of committed people can change the world.
Indeed, it is the only thing that ever has.
-----Original Message-----
From: Jeff Metcalf
Sent: Friday, September 28, 2007 8:08 AM
To: 'Finch Bill '; 'LazyDBA Discussion'
Subject: RE: Searching table
Replace all of the # with the @.
CREATE PROC SearchAllTables
(
#SearchStr nvarchar(100)
)
AS
BEGIN
-- Copyright (c) 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 + '%','''')
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
GO
Jeff D. Metcalf
IT Director
Cplus Sales & Service
864 801 9003
Never doubt that a small group of committed people can change the world.
Indeed, it is the only thing that ever has.
-----Original Message-----
From: Finch Bill
[mailto:mssqldba-ezmlmshield-x82731127.[Email address protected]
Sent: Friday, September 28, 2007 7:49 AM
To: LazyDBA Discussion
Subject: RE: Searching table
Clarification please,
Are you searching for a column of the name 'india', for example, or are
you looking for actual data in string type columns?
If you are looking for a column name you can use the following:
SELECT T.TABLE_NAME,C.COLUMN_NAME,C.DATA_TYPE FROM
INFORMATION_SCHEMA.COLUMNS C JOIN INFORMATION_SCHEMA.TABLES T ON
C.TABLE_NAME = T.TABLE_NAME WHERE C.COLUMN_NAME = '[Insert Column Name
Here]'
If you are looking for data, you can expand upon this example to create
a loop through all tables that contain string datatypes and build select
statements for each column that you need to search...
Bill Finch MCSE,CCNA
Database Administrator
LCG Technologies, Inc.
(443) 589-1820 - Direct Line
(443) 693-2589 - Cell
-----Original Message-----
From: J.Saraboji
[mailto:mssqldba-ezmlmshield-x77359658.[Email address protected]
Sent: Friday, September 28, 2007 7:21 AM
To: LazyDBA Discussion
Subject: Searching table
Dear Experts,
Can I check the tables using data?
Example
I have a data like 'India' now I have to search the all
tables.
Which table having the data name of 'India'.
Thanks & Regards,
JSaraboji,
SQL Database Administrator,
Direction Software Solutions,
Mumbai -
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba job: http://jobs.lazydba.com To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
This email, its electronic document attachments, and the contents of its
website linkages may contain confidential information. This information
is intended solely for use by the individual or entity to whom it is
addressed. If you have received this information in error, please notify
the sender immediately and arrange for the prompt destruction of the
material and any accompanying attachments.
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba job: http://jobs.lazydba.com To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
MS Sql Server LazyDBA home page