finding a string somewhere in the MS SQL user tables

Hi,
I have an application which shows a certain piece of data on the
screen, but I do not know which table it comes from (281 user tables).
How can I easily find which table has this data ?
(without opening each table and scanning through 1000's of rows
manually)
TIA
MichaelUse the profiler ... do the activity again to have a look at that
data ... In the backend it would definately show you the table or the
SP the application is calling ... Dig in further.
Regards
Bharat Butani.
On May 4, 3:39 pm, Chiken Koma <michaelnewp...@.yahoo.com> wrote:
> Hi,
> I have an application which shows a certain piece of data on the
> screen, but I do not know which table it comes from (281 user tables).
> How can I easily find which table has this data ?
> (without opening each table and scanning through 1000's of rows
> manually)
> TIA
> Michael|||If you now the column name you can do a search with object browser in query
analyzer on the database.
--
I drank alot of beer and ended up in the police department database.
Drank more beer and learned SQL in the dark hours.
DELETE FROM offenders WHERE Title=''MrAA'' AND Year=2006;
I love SQL
"fauxDBA@.gmail.com" wrote:

> Use the profiler ... do the activity again to have a look at that
> data ... In the backend it would definately show you the table or the
> SP the application is calling ... Dig in further.
>
> Regards
> Bharat Butani.
>
> On May 4, 3:39 pm, Chiken Koma <michaelnewp...@.yahoo.com> wrote:
>
>|||On 4 May, 13:47, faux...@.gmail.com wrote:[vbcol=seagreen]
> Use the profiler ... do the activity again to have a look at that
> data ... In the backend it would definately show you the table or the
> SP the application is calling ... Dig in further.
> Regards
> Bharat Butani.
> On May 4, 3:39 pm,ChikenKoma<michaelnewp...@.yahoo.com> wrote:
>
>
>
>
thanks I will try that|||On 4 May, 13:57, Hate_orphaned_users
<Hateorphanedus...@.discussions.microsoft.com> wrote:[vbcol=seagreen]
> If you now the column name you can do a search with object browser in quer
y
> analyzer on the database.
> --
> I drank alot of beer and ended up in the police department database.
> Drank more beer and learned SQL in the dark hours.
> DELETE FROM offenders WHERE Title=''MrAA'' AND Year=2006;
> I love SQL
> "faux...@.gmail.com" wrote:
>
>
>
>
>
thats the problem we dont know the table, in Ingres I used to dump the
whole database to ascii files and do a string search, this would
highlight a file which corresponded to a table, then I could search
probable columns in that table.|||The simplest way is the probably just to query the cataloguef or the tables,
processing the results to create select statement for each of the tables,
then run them until you find the column.
"Chiken Koma" <michaelnewport@.yahoo.com> wrote in message
news:1178275148.581808.186800@.l77g2000hsb.googlegroups.com...
> Hi,
> I have an application which shows a certain piece of data on the
> screen, but I do not know which table it comes from (281 user tables).
> How can I easily find which table has this data ?
> (without opening each table and scanning through 1000's of rows
> manually)
> TIA
> Michael
>|||If it just is the one time thing to search for a string, below is a SP
which I had written a long time back for fun (this is exactly similar
to what Mark had suggested). Use it rarely, and also not at the peak
time of the day.
----
---
DROP PROCEDURE FIND_IN_DB
GO
CREATE PROCEDURE FIND_IN_DB
@.SEARCHSTR VARCHAR(100),
@.EXACT_MATCH VARCHAR(1) = 'F',
@.MATCH_FULL_WORD VARCHAR(1) = 'F'
AS
BEGIN
DECLARE @.FROM INT
DECLARE @.TO INT
DECLARE @.TABLE_ID INT
DECLARE @.TABLE_NAME SYSNAME
DECLARE @.COLUMN_NAME SYSNAME
DECLARE @.OPERATOR SYSNAME
CREATE TABLE #TEMP_TABLE (
SLNO INT IDENTITY(1, 1) PRIMARY KEY,
TABLE_ID INT,
TABLE_NAME SYSNAME)
CREATE TABLE ##RESULT(
SLNO INT IDENTITY(1, 1) PRIMARY KEY,
TYPE VARCHAR(15),
TABLE_NAME VARCHAR(100),
COLUMN_NAME VARCHAR(300),
DATA_TEXT VARCHAR(7000) )
INSERT #TEMP_TABLE (TABLE_NAME, TABLE_ID)
SELECT NAME, ID FROM SYSOBJECTS WHERE XTYPE in ('U', 'S') ORDER
BY NAME
IF @.EXACT_MATCH <> 'T'
INSERT ##RESULT (TYPE, TABLE_NAME)
SELECT 'TABLE' AS TYPE, TABLE_NAME FROM #TEMP_TABLE
WHERE TABLE_NAME LIKE @.SEARCHSTR
ELSE
INSERT ##RESULT (TYPE, TABLE_NAME)
SELECT 'TABLE' AS TYPE, TABLE_NAME FROM #TEMP_TABLE
WHERE TABLE_NAME = @.SEARCHSTR
SET @.FROM = 1
SET @.TO = (SELECT MAX(SLNO) FROM #TEMP_TABLE)
IF @.EXACT_MATCH = 'F'
BEGIN
IF @.MATCH_FULL_WORD = 'T'
SET @.SEARCHSTR = '%[^a-z,^0-9,_]' +
RTRIM(LTRIM(@.SEARCHSTR)) + '[^a-z,^0-9,_]%'
ELSE
SET @.SEARCHSTR = '%' + @.SEARCHSTR + '%'
SELECT @.OPERATOR = ' LIKE '
END
ELSE
SELECT @.OPERATOR = ' = '
WHILE @.FROM <= @.TO
BEGIN
SELECT @.TABLE_NAME = TABLE_NAME , @.TABLE_ID = TABLE_ID FROM
#TEMP_TABLE WHERE SLNO = @.FROM
SELECT NAME AS COLUMN_NAME INTO #TEMP_COLUMNS FROM SYSCOLUMNS
WHERE ID = @.TABLE_ID AND XTYPE = 167 ORDER BY XTYPE DESC
IF @.EXACT_MATCH <> 'T'
INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME, DATA_TEXT)
SELECT 'COLUMN' AS TYPE, @.TABLE_NAME AS TABLE_NAME,
COLUMN_NAME, COLUMN_NAME FROM #TEMP_COLUMNS
WHERE COLUMN_NAME LIKE @.SEARCHSTR
ELSE
INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME, DATA_TEXT)
SELECT 'COLUMN' AS TYPE, @.TABLE_NAME AS TABLE_NAME,
COLUMN_NAME, COLUMN_NAME FROM #TEMP_COLUMNS
WHERE COLUMN_NAME = @.SEARCHSTR
WHILE EXISTS(SELECT TOP 1 COLUMN_NAME FROM #TEMP_COLUMNS ORDER
BY COLUMN_NAME)
BEGIN
SET @.COLUMN_NAME = (SELECT TOP 1 COLUMN_NAME FROM
#TEMP_COLUMNS ORDER BY COLUMN_NAME)
EXECUTE('INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME,
DATA_TEXT)
SELECT ''DATA'' AS TYPE, ''' + @.TABLE_NAME + ''' AS
TABLE_NAME, ''' + @.COLUMN_NAME + ''', '+ @.COLUMN_NAME
+ ' FROM ' + @.TABLE_NAME + ' WHERE ' + @.COLUMN_NAME + ' '
+ @.OPERATOR + '''' + @.SEARCHSTR + '''')
DELETE #TEMP_COLUMNS WHERE COLUMN_NAME = @.COLUMN_NAME
END
DROP TABLE #TEMP_COLUMNS
SET @.FROM = @.FROM + 1
END
SELECT * FROM ##RESULT
DROP TABLE #TEMP_TABLE
DROP TABLE ##RESULT
END
GO
-- EXEC FIND_IN_DB 'test%', 'F', 'T'
----
---|||On 6 May, 12:57, nime...@.gmail.com wrote:
> If it just is the one time thing to search for a string, below is a SP
> which I had written a long time back for fun (this is exactly similar
> to what Mark had suggested). Use it rarely, and also not at the peak
> time of the day.
> ----
---
> DROP PROCEDURE FIND_IN_DB
> GO
> CREATE PROCEDURE FIND_IN_DB
> @.SEARCHSTR VARCHAR(100),
> @.EXACT_MATCH VARCHAR(1) = 'F',
> @.MATCH_FULL_WORD VARCHAR(1) = 'F'
> AS
> BEGIN
> DECLARE @.FROM INT
> DECLARE @.TO INT
> DECLARE @.TABLE_ID INT
> DECLARE @.TABLE_NAME SYSNAME
> DECLARE @.COLUMN_NAME SYSNAME
> DECLARE @.OPERATOR SYSNAME
> CREATE TABLE #TEMP_TABLE (
> SLNO INT IDENTITY(1, 1) PRIMARY KEY,
> TABLE_ID INT,
> TABLE_NAME SYSNAME)
> CREATE TABLE ##RESULT(
> SLNO INT IDENTITY(1, 1) PRIMARY KEY,
> TYPE VARCHAR(15),
> TABLE_NAME VARCHAR(100),
> COLUMN_NAME VARCHAR(300),
> DATA_TEXT VARCHAR(7000) )
> INSERT #TEMP_TABLE (TABLE_NAME, TABLE_ID)
> SELECT NAME, ID FROM SYSOBJECTS WHERE XTYPE in ('U', 'S') ORDER
> BY NAME
> IF @.EXACT_MATCH <> 'T'
> INSERT ##RESULT (TYPE, TABLE_NAME)
> SELECT 'TABLE' AS TYPE, TABLE_NAME FROM #TEMP_TABLE
> WHERE TABLE_NAME LIKE @.SEARCHSTR
> ELSE
> INSERT ##RESULT (TYPE, TABLE_NAME)
> SELECT 'TABLE' AS TYPE, TABLE_NAME FROM #TEMP_TABLE
> WHERE TABLE_NAME = @.SEARCHSTR
> SET @.FROM = 1
> SET @.TO = (SELECT MAX(SLNO) FROM #TEMP_TABLE)
> IF @.EXACT_MATCH = 'F'
> BEGIN
> IF @.MATCH_FULL_WORD = 'T'
> SET @.SEARCHSTR = '%[^a-z,^0-9,_]' +
> RTRIM(LTRIM(@.SEARCHSTR)) + '[^a-z,^0-9,_]%'
> ELSE
> SET @.SEARCHSTR = '%' + @.SEARCHSTR + '%'
> SELECT @.OPERATOR = ' LIKE '
> END
> ELSE
> SELECT @.OPERATOR = ' = '
> WHILE @.FROM <= @.TO
> BEGIN
> SELECT @.TABLE_NAME = TABLE_NAME , @.TABLE_ID = TABLE_ID FROM
> #TEMP_TABLE WHERE SLNO = @.FROM
> SELECT NAME AS COLUMN_NAME INTO #TEMP_COLUMNS FROM SYSCOLUMNS
> WHERE ID = @.TABLE_ID AND XTYPE = 167 ORDER BY XTYPE DESC
> IF @.EXACT_MATCH <> 'T'
> INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME, DATA_TEXT)
> SELECT 'COLUMN' AS TYPE, @.TABLE_NAME AS TABLE_NAME,
> COLUMN_NAME, COLUMN_NAME FROM #TEMP_COLUMNS
> WHERE COLUMN_NAME LIKE @.SEARCHSTR
> ELSE
> INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME, DATA_TEXT)
> SELECT 'COLUMN' AS TYPE, @.TABLE_NAME AS TABLE_NAME,
> COLUMN_NAME, COLUMN_NAME FROM #TEMP_COLUMNS
> WHERE COLUMN_NAME = @.SEARCHSTR
> WHILE EXISTS(SELECT TOP 1 COLUMN_NAME FROM #TEMP_COLUMNS ORDER
> BY COLUMN_NAME)
> BEGIN
> SET @.COLUMN_NAME = (SELECT TOP 1 COLUMN_NAME FROM
> #TEMP_COLUMNS ORDER BY COLUMN_NAME)
> EXECUTE('INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME,
> DATA_TEXT)
> SELECT ''DATA'' AS TYPE, ''' + @.TABLE_NAME + ''' AS
> TABLE_NAME, ''' + @.COLUMN_NAME + ''', '+ @.COLUMN_NAME
> + ' FROM ' + @.TABLE_NAME + ' WHERE ' + @.COLUMN_NAME + ' '
> + @.OPERATOR + '''' + @.SEARCHSTR + '''')
> DELETE #TEMP_COLUMNS WHERE COLUMN_NAME = @.COLUMN_NAME
> END
> DROP TABLE #TEMP_COLUMNS
> SET @.FROM = @.FROM + 1
> END
> SELECT * FROM ##RESULT
> DROP TABLE #TEMP_TABLE
> DROP TABLE ##RESULT
> END
> GO
> -- EXEC FIND_IN_DB 'test%', 'F', 'T'
> ----
----
thanks I will try that