October 17, 2007 at 9:41 am
My company has just picked up a new app and I'm trying to figure out how all the tables work together / where bits of information is stored. I had created a query awhile ago that nest a couple cursors -
get the list of tables
- - get the list of columns for a table
- - - - Search each column for the supplied string*
Its not a terribly efficient query, it takes 3 steps but for smallish databases it gets the job done. But the problem spots I've encountered, all boil down to the same thing - how can you save the results of a variable query? ie select @x = exec('select count(*) from table '+ @table +' where .....')
* the code actually prints to the screen a list of select statements created from the variable queries. You copy and paste them into QA and run the code. You are then given output of all the tables that actually contain the string and can run that code.
Basically what I've got works and I can live with it, but I'm curious if there is a way to do it. If there is a better approach I'm all ears, but I'm still curious about the above questions.
Don't know if it will help anybody, but the current incarnation is:
DECLARE @STR varchar(255), @count int, @table varchar(255)
set @STR = 'Txt Here'
set @count = 0
DECLARE c1 CURSOR FOR
select name from sysobjects where type='U'
OPEN c1
print 'declare @x as int' -- necessary to initalize when pasting in the 2nd run.
FETCH NEXT FROM c1 INTO @table
WHILE @@FETCH_STATUS = 0
BEGIN
-- for each table name, get the columns from it
DECLARE p1 CURSOR FOR
select name from syscolumns where id =object_id(@table)
OPEN p1
DECLARE @column varchar(255)
FETCH NEXT FROM p1
INTO @column
WHILE @@FETCH_STATUS = 0
BEGIN
declare @query as varchar(500)
set @count = @count + 1
print ' -- ' + cast(@count as varchar) -- just a placeholder.
print ' select @x = count(*) from ' + @table + ' where ' + @column + ' Like ''%'+ @STR + '%'' '
print ' if @x > 0 print '' select count(*) from ' + @table + ' WITH (NOLOCK) where ' + @column + ' Like ''''%'+ @STR + '%'''' '' '
FETCH NEXT FROM p1
INTO @column
END
CLOSE p1
DEALLOCATE p1
FETCH NEXT FROM c1
INTO @table
END
CLOSE c1
DEALLOCATE c1
October 17, 2007 at 11:01 am
OK so maybe it's just me, but it seems almost like you're reinventing the wheel here.
If you're looking for a column or whatever other type of object in your database that matches a string, why not use the object browser (F8) and Object Search (F4) Functiosn that are already built into QA? Or perhaps I'm just missing the point?
As for saving a variable from a Query, you could just Print (@myVar) and copy and paste it from the results pan...
-Luke.
October 17, 2007 at 12:23 pm
May not have been clear, but I'm actually using this to scan through the actual data. The code I posted was originally used to count the number of times the string appears in the column as a way to zero in on specific columns and tables
output from 1st run:
declare @x as int
-- 1
select @x = count(*) from performance_day_data where account_id Like '%comp%'
if @x > 0 print ' select count(*) from performance_day_data WITH (NOLOCK) where account_id Like ''%comp%'' '
-- 2
select @x = count(*) from performance_day_data where computer_id Like '%comp%'
if @x > 0 print ' select count(*) from performance_day_data WITH (NOLOCK) where computer_id Like ''%comp%'' '
-- 3
select @x = count(*) from performance_day_data where check_name Like '%comp%'
if @x > 0 print ' select count(*) from performance_day_data WITH (NOLOCK) where check_name Like ''%comp%'' '
-- 4
select @x = count(*) from performance_day_data where idx Like '%comp%'
if @x > 0 print ' select count(*) from performance_day_data WITH (NOLOCK) where idx Like ''%comp%'' '
-- 5
select @x = count(*) from computer where inventory_xml Like '%comp%'
if @x > 0 print ' select count(*) from performance_day_data WITH (NOLOCK) where idx Like ''%comp%'' '
.... and so on. Copy all this into QA and run again.
If there are any matches the output will only print those instances that actually have the value in a column
select count(*) from computer WITH (NOLOCK) where inventory_xml Like '%comp%'
select count(*) from system_user WITH (NOLOCK) where user_conf Like '%comp%'
Then the final output will list the counts each column had.
See, at this point I have NO idea what the columns are in terms of name or data much less which ones are relevant. But I can take a userID, search through the DB for each table/column it appears in and then have perhaps a dozen places to look instead of 60 tables, each with a fair number of columns.
Its not a query you'd use often, but it has its uses. I'd like to automate this to 1 step if possible but am stumpped at this point
October 17, 2007 at 1:19 pm
this might help;
i create a proc i called "uglysearch" which searches every varchar,char,nvarchar and nchar column for a string.
it returns the table and column, as well as the sql to query the records for that specific table:
Typical Results:
[font="Courier New"]CMCONTACTBUSEMAIL SELECT * FROM [CMCONTACT] WHERE [BUSEMAIL] LIKE 'TEST'
CMCONTACTFIRSTNAME SELECT * FROM [CMCONTACT] WHERE [FIRSTNAME] LIKE 'TEST'
CMCONTACTLASTNAME SELECT * FROM [CMCONTACT] WHERE [LASTNAME] LIKE 'TEST'
GMATDET NOTE SELECT * FROM [GMATDET] WHERE LIKE 'TEST' [/font]
ALTER PROCEDURE UGLYSEARCH
-- EXEC UGLYSEARCH 'TEST'
@SEARCHSTRING VARCHAR(50)
AS
SET NOCOUNT ON
DECLARE @sql VARCHAR(500),
@TABLENAME VARCHAR(60),
@COLUMNNAME VARCHAR(60)
CREATE TABLE #RESULTS(TBLNAME VARCHAR(60),COLNAME VARCHAR(60),SQL VARCHAR(700))
SELECT
SYSOBJECTS.NAME AS TBLNAME,
SYSCOLUMNS.NAME AS COLNAME,
TYPE_NAME(SYSCOLUMNS.XTYPE) AS DATATYPE
INTO #FKFINDER
FROM SYSOBJECTS
INNER JOIN SYSCOLUMNS ON SYSOBJECTS.ID=SYSCOLUMNS.ID
WHERE SYSOBJECTS.XTYPE='U'
AND TYPE_NAME(SYSCOLUMNS.XTYPE) IN ('VARCHAR','NVARCHAR','CHAR','NCHAR')
ORDER BY TBLNAME,COLNAME
DECLARE C1 CURSOR FOR
SELECT TBLNAME,COLNAME FROM #FKFINDER ORDER BY TBLNAME,COLNAME
OPEN C1
FETCH NEXT FROM C1 INTO @TABLENAME,@COLUMNNAME
WHILE @@FETCH_STATUS <> -1
BEGIN
--SET @sql = 'SELECT ''' + @TABLENAME + ''' AS TABLENAME,''' + @COLUMNNAME + ''' AS COLUMNNAME,* FROM ' + @TABLENAME + ' WHERE ' + @COLUMNNAME + ' LIKE ''%' + @SEARCHSTRING + '%'''
SET @sql = 'IF EXISTS(SELECT * FROM [' + @TABLENAME + '] WHERE [' + @COLUMNNAME + '] LIKE ''%' + @SEARCHSTRING + '%'') INSERT INTO #RESULTS(TBLNAME,COLNAME,SQL) VALUES(''' + @TABLENAME + ''',''' + @COLUMNNAME + ''','' SELECT * FROM [' + @TABLENAME + '] WHERE [' + @COLUMNNAME + '] LIKE ''''' + @SEARCHSTRING + ''''' '') ;'
--PRINT @sql
EXEC (@SQL)
FETCH NEXT FROM C1 INTO @TABLENAME,@COLUMNNAME
END
CLOSE C1
DEALLOCATE C1
SELECT * FROM #RESULTS
GO
Lowell
October 17, 2007 at 1:41 pm
Thats perfect! I like how you only target the varchar etc fields and ignore the binary ones - they gave me some grief with the previous code. While you call it ugly search, the results are much more elegant then what I was doing previously.
I don't work with temp tables too often so do you mind if I ask a couple questions?
being a temp table, the system takes care of removing the table once the code is finished running correct?
- do you ever need to worry about code running simultaneously using the same temp name?
Thanks again for the code!
October 17, 2007 at 1:53 pm
with the clarification I understand what you're getting at now. Lowell's strategy is pretty elegant.
As for the temp table bit, yes, the system stores the table in memory for the length of the connection, so once the procedure completes the temp tables removed from memory.
Yes, you can run this procedure simultaneously, because each temp table name actually has a bit of connection info appended to the end of it.
If you open a new QA window and type CREATE TABLE #temp (col1 int) and then refresh the object browser you'll see the information that gets appended to the end of the #temp name.
-Luke.
October 17, 2007 at 2:12 pm
Luke identified that it's no problem running it concurrently.
i call it ugly search becauuse it's not something you want production users to use, and image you have a 12 million row table, and that table features a two dozen or more varchar fields....
it's an ugly, ham handed way to search, but in certain situations, it's what you gotta do.
24+ scans on the same table....ugh.
i have a similar one that searched just for money datatypes, because someone in accounting KNEW they put in a specific amount with a repeating number.... 15555 should have been 1555, but then got distracted with phone calls and meetings, and at the end of the day couldn't remember what screen she was data entering. so i had to find every money data type that had 15555. in it just to identify what screen might have been the one, so an error could be corrected in data entry....no other easy way to figure that one out.
Lowell
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply