How to find strings in multiple tables

I have a database in SQL has 250 tables. I want to find out all the tables
which have certain string in it for example "computer1". Is there an easier
way to do this task rather than going into each table do a search?JL
This script has written by Narayana Vyas Kondreddi. Also I suggest to visit
his site (http://vyaskn.tripod.com )when you find a lot of examples and
useful scripts.
CREATE PROC SearchAllTables
(
@.SearchStr nvarchar(100)
)
AS
BEGIN
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
"JL" <ljmagzine@.hotmail.com> wrote in message
news:OuCRClDTEHA.3872@.TK2MSFTNGP10.phx.gbl...
> I have a database in SQL has 250 tables. I want to find out all the tables
> which have certain string in it for example "computer1". Is there an
easier
> way to do this task rather than going into each table do a search?
>|||Hi,
Have a look into the below code, I got this procedure from previous post.
This code will generate the Select statement for all the
Char, varchar, nvarchar,nchar columns for all the tables searching for the
string you are inputting.
All you have to do is just cut and paste the result of this procedure to a
query window and execute.
---
drop procedure ColSearch
go
create procedure ColSearch @.instr varchar(255), @.tablename varchar(255) =null
as
create table #colsearch (colid int identity (1,1),
colname varchar(255),
tablename varchar(255))
if @.tablename is not null
begin
insert #colsearch (colname, tablename)
select sc.name, so.name
from sysobjects so inner join syscolumns sc
on so.id = sc.id
where so.type = 'u'
and so.name = @.tablename
and type_name(sc.xusertype) in ('varchar', 'char', 'nvarchar', 'nchar')
and datalength(@.instr) <= sc.length
order by so.name, sc.colid
end
else
begin
insert #colsearch (colname, tablename)
select sc.name, so.name
from sysobjects so inner join syscolumns sc
on so.id = sc.id
where so.type = 'u'
and type_name(sc.xusertype) in ('varchar', 'char', 'nvarchar', 'nchar')
and datalength(@.instr) <= sc.length
order by so.name, sc.colid
end
select 'select '+rtrim(colname)+' from '+rtrim(tablename)+' where
'+colname+' like ''%'+@.instr+'%'''
from #colsearch
order by colid
Thanks
Hari
MCDBA
"JL" <ljmagzine@.hotmail.com> wrote in message
news:OuCRClDTEHA.3872@.TK2MSFTNGP10.phx.gbl...
> I have a database in SQL has 250 tables. I want to find out all the tables
> which have certain string in it for example "computer1". Is there an
easier
> way to do this task rather than going into each table do a search?
>

How to find strings in multiple tables

I have a database in SQL has 250 tables. I want to find out all the tables
which have certain string in it for example "computer1". Is there an easier
way to do this task rather than going into each table do a search?JL
This script has written by Narayana Vyas Kondreddi. Also I suggest to visit
his site (http://vyaskn.tripod.com )when you find a lot of examples and
useful scripts.
CREATE PROC SearchAllTables
(
@.SearchStr nvarchar(100)
)
AS
BEGIN
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
"JL" <ljmagzine@.hotmail.com> wrote in message
news:OuCRClDTEHA.3872@.TK2MSFTNGP10.phx.gbl...
> I have a database in SQL has 250 tables. I want to find out all the tables
> which have certain string in it for example "computer1". Is there an
easier
> way to do this task rather than going into each table do a search?
>|||Hi,
Have a look into the below code, I got this procedure from previous post.
This code will generate the Select statement for all the
Char, varchar, nvarchar,nchar columns for all the tables searching for the
string you are inputting.
All you have to do is just cut and paste the result of this procedure to a
query window and execute.
---
drop procedure ColSearch
go
create procedure ColSearch @.instr varchar(255), @.tablename varchar(255) =null
as
create table #colsearch (colid int identity (1,1),
colname varchar(255),
tablename varchar(255))
if @.tablename is not null
begin
insert #colsearch (colname, tablename)
select sc.name, so.name
from sysobjects so inner join syscolumns sc
on so.id = sc.id
where so.type = 'u'
and so.name = @.tablename
and type_name(sc.xusertype) in ('varchar', 'char', 'nvarchar', 'nchar')
and datalength(@.instr) <= sc.length
order by so.name, sc.colid
end
else
begin
insert #colsearch (colname, tablename)
select sc.name, so.name
from sysobjects so inner join syscolumns sc
on so.id = sc.id
where so.type = 'u'
and type_name(sc.xusertype) in ('varchar', 'char', 'nvarchar', 'nchar')
and datalength(@.instr) <= sc.length
order by so.name, sc.colid
end
select 'select '+rtrim(colname)+' from '+rtrim(tablename)+' where
'+colname+' like ''%'+@.instr+'%'''
from #colsearch
order by colid
Thanks
Hari
MCDBA
"JL" <ljmagzine@.hotmail.com> wrote in message
news:OuCRClDTEHA.3872@.TK2MSFTNGP10.phx.gbl...
> I have a database in SQL has 250 tables. I want to find out all the tables
> which have certain string in it for example "computer1". Is there an
easier
> way to do this task rather than going into each table do a search?
>

How to find strings in multiple tables

I have a database in SQL has 250 tables. I want to find out all the tables
which have certain string in it for example "computer1". Is there an easier
way to do this task rather than going into each table do a search?
JL
This script has written by Narayana Vyas Kondreddi. Also I suggest to visit
his site (http://vyaskn.tripod.com )when you find a lot of examples and
useful scripts.
CREATE PROC SearchAllTables
(
@.SearchStr nvarchar(100)
)
AS
BEGIN
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
"JL" <ljmagzine@.hotmail.com> wrote in message
news:OuCRClDTEHA.3872@.TK2MSFTNGP10.phx.gbl...
> I have a database in SQL has 250 tables. I want to find out all the tables
> which have certain string in it for example "computer1". Is there an
easier
> way to do this task rather than going into each table do a search?
>
|||Hi,
Have a look into the below code, I got this procedure from previous post.
This code will generate the Select statement for all the
Char, varchar, nvarchar,nchar columns for all the tables searching for the
string you are inputting.
All you have to do is just cut and paste the result of this procedure to a
query window and execute.
drop procedure ColSearch
go
create procedure ColSearch @.instr varchar(255), @.tablename varchar(255) =
null
as
create table #colsearch (colid int identity (1,1),
colname varchar(255),
tablename varchar(255))
if @.tablename is not null
begin
insert #colsearch (colname, tablename)
select sc.name, so.name
from sysobjects so inner join syscolumns sc
on so.id = sc.id
where so.type = 'u'
and so.name = @.tablename
and type_name(sc.xusertype) in ('varchar', 'char', 'nvarchar', 'nchar')
and datalength(@.instr) <= sc.length
order by so.name, sc.colid
end
else
begin
insert #colsearch (colname, tablename)
select sc.name, so.name
from sysobjects so inner join syscolumns sc
on so.id = sc.id
where so.type = 'u'
and type_name(sc.xusertype) in ('varchar', 'char', 'nvarchar', 'nchar')
and datalength(@.instr) <= sc.length
order by so.name, sc.colid
end
select 'select '+rtrim(colname)+' from '+rtrim(tablename)+' where
'+colname+' like ''%'+@.instr+'%'''
from #colsearch
order by colid
Thanks
Hari
MCDBA
"JL" <ljmagzine@.hotmail.com> wrote in message
news:OuCRClDTEHA.3872@.TK2MSFTNGP10.phx.gbl...
> I have a database in SQL has 250 tables. I want to find out all the tables
> which have certain string in it for example "computer1". Is there an
easier
> way to do this task rather than going into each table do a search?
>

How to find strings in multiple tables

I have a database in SQL has 250 tables. I want to find out all the tables
which have certain string in it for example "computer1". Is there an easier
way to do this task rather than going into each table do a search?JL
This script has written by Narayana Vyas Kondreddi. Also I suggest to visit
his site (http://vyaskn.tripod.com )when you find a lot of examples and
useful scripts.
CREATE PROC SearchAllTables
(
@.SearchStr nvarchar(100)
)
AS
BEGIN
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
"JL" <ljmagzine@.hotmail.com> wrote in message
news:OuCRClDTEHA.3872@.TK2MSFTNGP10.phx.gbl...
> I have a database in SQL has 250 tables. I want to find out all the tables
> which have certain string in it for example "computer1". Is there an
easier
> way to do this task rather than going into each table do a search?
>|||Hi,
Have a look into the below code, I got this procedure from previous post.
This code will generate the Select statement for all the
Char, varchar, nvarchar,nchar columns for all the tables searching for the
string you are inputting.
All you have to do is just cut and paste the result of this procedure to a
query window and execute.
---
drop procedure ColSearch
go
create procedure ColSearch @.instr varchar(255), @.tablename varchar(255) =
null
as
create table #colsearch (colid int identity (1,1),
colname varchar(255),
tablename varchar(255))
if @.tablename is not null
begin
insert #colsearch (colname, tablename)
select sc.name, so.name
from sysobjects so inner join syscolumns sc
on so.id = sc.id
where so.type = 'u'
and so.name = @.tablename
and type_name(sc.xusertype) in ('varchar', 'char', 'nvarchar', 'nchar')
and datalength(@.instr) <= sc.length
order by so.name, sc.colid
end
else
begin
insert #colsearch (colname, tablename)
select sc.name, so.name
from sysobjects so inner join syscolumns sc
on so.id = sc.id
where so.type = 'u'
and type_name(sc.xusertype) in ('varchar', 'char', 'nvarchar', 'nchar')
and datalength(@.instr) <= sc.length
order by so.name, sc.colid
end
select 'select '+rtrim(colname)+' from '+rtrim(tablename)+' where
'+colname+' like ''%'+@.instr+'%'''
from #colsearch
order by colid
Thanks
Hari
MCDBA
"JL" <ljmagzine@.hotmail.com> wrote in message
news:OuCRClDTEHA.3872@.TK2MSFTNGP10.phx.gbl...
> I have a database in SQL has 250 tables. I want to find out all the tables
> which have certain string in it for example "computer1". Is there an
easier
> way to do this task rather than going into each table do a search?
>

How to find max from a table by row

I have a Product table with the columns

AcctNum
ProdCode
InvoiceDate

I can have multiple rows for a given AcctNum:

123 A 01/01/2005
123 B 01/02/2005
123 C 01/03/2005
234 C 02/01/2004
345 A 01/01/2005
345 B 01/02/2005

I need the max(InvoiceDate) and if the max for a given AcctNum is a ProdCode
= B. So if the latest InvoiceDate is for a given AcctNum is B then return
that row.

123 B 01/02/2005
Would not be returned because the max Invoice date for AcctNum 123 is
ProdCode C.

345 B 01/02/2005
Would be returned because the max Invoice date for AcctNum 345 is ProdCode
B.

I can solve this using a cursor fairly easily by using a distinct AcctNum in
the cursor select and getting the max InvoiceDate for each AcctNum. This is
a costly and I'm looking for a solution using temp tables or a query to
handle this problem.

I hope I have made this clear enough (sorry if I was too verbose). Thanks in
advance for your help.

-pHi

Try

SELECT A.AcctNum, A.ProdCode, A.InvoiceDate
FROM MyAccts A
WHERE ProdCode = 'B'
AND NOT EXISTS ( SELECT 1 FROM MyAccts B WHERE A.AcctNum = B.AcctNum AND
B.InvoiceDate > A.InvoiceDate )

or

SELECT A.AcctNum, A.ProdCode, A.InvoiceDate
FROM MyAccts A
WHERE ProdCode = 'B'
AND A.InvoiceDate = ( SELECT MAX(B.InvoiceDate) FROM MyAccts B WHERE
A.AcctNum = B.AcctNum )

Also check out how to post DDL and example data at
http://www.aspfaq.com/etiquett*e.asp?id=5006 and
example data as insert statements http://vyaskn.tripod.com/code.*htm#inserts
It is also useful to post your current attempts at solving the problem.

John
"Pippen" <name@.notreal.add> wrote in message
news:O9GdnTvxA-1jPqHfRVn-iw@.comcast.com...
>I have a Product table with the columns
> AcctNum
> ProdCode
> InvoiceDate
> I can have multiple rows for a given AcctNum:
> 123 A 01/01/2005
> 123 B 01/02/2005
> 123 C 01/03/2005
> 234 C 02/01/2004
> 345 A 01/01/2005
> 345 B 01/02/2005
> I need the max(InvoiceDate) and if the max for a given AcctNum is a
> ProdCode = B. So if the latest InvoiceDate is for a given AcctNum is B
> then return that row.
> 123 B 01/02/2005
> Would not be returned because the max Invoice date for AcctNum 123 is
> ProdCode C.
> 345 B 01/02/2005
> Would be returned because the max Invoice date for AcctNum 345 is ProdCode
> B.
> I can solve this using a cursor fairly easily by using a distinct AcctNum
> in the cursor select and getting the max InvoiceDate for each AcctNum.
> This is a costly and I'm looking for a solution using temp tables or a
> query to handle this problem.
> I hope I have made this clear enough (sorry if I was too verbose). Thanks
> in advance for your help.
> -p
>|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:423d73a3$0$32610$db0fefd9@.news.zen.co.uk...
> Hi
> Try
> SELECT A.AcctNum, A.ProdCode, A.InvoiceDate
> FROM MyAccts A
> WHERE ProdCode = 'B'
> AND NOT EXISTS ( SELECT 1 FROM MyAccts B WHERE A.AcctNum = B.AcctNum AND
> B.InvoiceDate > A.InvoiceDate )
> or
> SELECT A.AcctNum, A.ProdCode, A.InvoiceDate
> FROM MyAccts A
> WHERE ProdCode = 'B'
> AND A.InvoiceDate = ( SELECT MAX(B.InvoiceDate) FROM MyAccts B WHERE
> A.AcctNum = B.AcctNum )
> Also check out how to post DDL and example data at
> http://www.aspfaq.com/etiquett*e.asp?id=5006 and
> example data as insert statements
> http://vyaskn.tripod.com/code.*htm#inserts
> It is also useful to post your current attempts at solving the problem.
> John
> "Pippen" <name@.notreal.add> wrote in message
> news:O9GdnTvxA-1jPqHfRVn-iw@.comcast.com...
>>I have a Product table with the columns
>>
>> AcctNum
>> ProdCode
>> InvoiceDate
>>
>> I can have multiple rows for a given AcctNum:
>>
>> 123 A 01/01/2005
>> 123 B 01/02/2005
>> 123 C 01/03/2005
>> 234 C 02/01/2004
>> 345 A 01/01/2005
>> 345 B 01/02/2005
>>
>> I need the max(InvoiceDate) and if the max for a given AcctNum is a
>> ProdCode = B. So if the latest InvoiceDate is for a given AcctNum is B
>> then return that row.
>>
>> 123 B 01/02/2005
>> Would not be returned because the max Invoice date for AcctNum 123 is
>> ProdCode C.
>>
>> 345 B 01/02/2005
>> Would be returned because the max Invoice date for AcctNum 345 is
>> ProdCode B.
>>
>> I can solve this using a cursor fairly easily by using a distinct AcctNum
>> in the cursor select and getting the max InvoiceDate for each AcctNum.
>> This is a costly and I'm looking for a solution using temp tables or a
>> query to handle this problem.
>>
>> I hope I have made this clear enough (sorry if I was too verbose). Thanks
>> in advance for your help.
>>
>> -p
>
Thanks for the help.

-p