Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Feb 2005
    Posts
    10

    Unanswered: need to find where in db record is stored

    I have a mystery database on my hands...it is part of an application that my company bought, and it uses SQL server for its backend. The reporting features built in are not good enough, so I need to write some queries by hand...trouble is I am having a hard time figuring out how the schema works...using the front end they gave us I put a value of "12345" for a field I need to get to, but I can not locate where in the db it gets stored....can anyone tell me a way to query that will look at every single record and every single field in the db to find the value "12345"??

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Happy Birthday

    You've been Blogged


    Code:
    USE Northwind
    GO
    
    SET NOCOUNT OFF
    CREATE TABLE myTableSearch99(
    	  SPID int
    	, SearchDate datetime
    	, TABLE_CATALOG sysname
    	, TABLE_SCHEMA sysname
    	, TABLE_NAME sysname
    	, COLUMN_NAME varchar(255)
    	, SEARCHARG varchar(2000)
    	, RESULTS varchar(4000))
    GO
    
    CREATE PROC myDBSearch99
    	@SearchArg varchar(2000)
    AS
      BEGIN
    	SET NOCOUNT ON
    	DECLARE @sql varchar(8000), @Date datetime
    	DECLARE @TABLE_CATALOG sysname, @TABLE_SCHEMA sysname, @TABLE_NAME sysname, @COLUMN_NAME sysname
    	SELECT @date = GetDate()	
    
    	DECLARE myCursor99 CURSOR FOR
    		SELECT c.TABLE_CATALOG, c.TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME 
    		  FROM INFORMATION_SCHEMA.Columns c 
    	    INNER JOIN INFORMATION_SCHEMA.Tables t
    		    ON c.TABLE_CATALOG = t.TABLE_CATALOG
    		   AND c.TABLE_SCHEMA  = t.TABLE_SCHEMA
    		   AND c. TABLE_NAME   = t.TABLE_NAME
    		 WHERE t.TABLE_TYPE = 'BASE TABLE'
    		   AND c.DATA_TYPE IN ('char','nchar','varchar','nvarchar','datetime') 
    
    	OPEN myCursor99
    	FETCH NEXT FROM myCursor99 INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME, @COLUMN_NAME
    
    	INSERT INTO myTableSearch99(SPID,SearchDate,TABLE_CATALOG,TABLE_SCHEMA, TABLE_NAME, SEARCHARG)
    	SELECT @@SPID, CONVERT(varchar(25),@Date,120), @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME, @SearchArg
    
    	WHILE @@FETCH_STATUS = 0
    	  BEGIN
    		SELECT @sql = 'INSERT INTO myTableSearch99(SPID,SearchDate,TABLE_CATALOG,TABLE_SCHEMA, ' + 
    				+ 'TABLE_NAME,COLUMN_NAME,SEARCHARG,RESULTS) '
    				+ 'SELECT ' + CONVERT(varchar(15),@@SPID) + ',' + '''' 
    				+ CONVERT(varchar(25),@Date,120) + '''' + ',' + '''' +
    				+ @TABLE_CATALOG + ''',''' + @TABLE_SCHEMA + ''',''' +  @TABLE_NAME + ''',' + '''' 
    				+ @COLUMN_NAME + ''',' + ''''+ @SearchArg + '''' + ',' + @COLUMN_NAME
    				+ ' FROM ' + '['+ @TABLE_CATALOG + '].[' + @TABLE_SCHEMA + '].[' +  @TABLE_NAME + '] '
    				+ ' WHERE ' + @COLUMN_NAME + ' LIKE ''%' + @SearchArg + '%''' 
    --		SELECT @sql
    		EXEC(@sql)
    		FETCH NEXT FROM myCursor99 INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME, @COLUMN_NAME
    	  END
    
    	SET NOCOUNT OFF
    
    
    	 SELECT 	  
    		  SPID
    		, SearchDate
    		, TABLE_CATALOG
    		, TABLE_SCHEMA
    		, TABLE_NAME
    		, COLUMN_NAME
    		, SEARCHARG
    		, RESULTS
    	  FROM    myTableSearch99 o
    	 WHERE SPID = @@SPID 
    	   AND SearchDate = ( SELECT MAX(SearchDate) 
    				FROM myTableSearch99 i
    			       WHERE i.SPID = o.SPID)
    	CLOSE myCursor99
    	DEALLOCATE myCursor99  
      END
    GO
    
    EXEC myDBSearch99 'Ale'
    GO
    
    EXEC myDBSearch99 'Stout'
    GO
    
    SELECT * FROM myTableSearch99 ORDER BY SearchDate DESC, COLUMN_NAME
    GO
    
    SET NOCOUNT OFF
    TRUNCATE TABLE myTableSearch99
    DROP TABLE myTableSearch99
    DROP PROC myDBSearch99
    Last edited by Brett Kaiser; 02-08-05 at 14:21.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Come on Mikey...all that work and you disappeared....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    He's probably still waiting for the stored proc to finish!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    hey...I just code to spec....

    Can you imagine if the search criteria was something very generic
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    On a similiar note, I wrote a script today to take any table and identify all the natural keys in it. Handy when walking into somebody else's database environment.

    Dynamic SQL with an iterative cursor, so I'm a bad boy too.

    It works, but performance on a table with more than 20 columns really drags.

    Permutations and factorials are a bitch, man!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Feb 2005
    Posts
    10
    sorry I took so long to reply. I have been using the script successfully, and I appreciate the rapid turn around time. I was using it minutes after you posted it, and it works wonderful. I didn't get to thanking you right away because even with your script's help I wasn't able to solve the problem I was working on right away. ...so, I been busy, I tend not to stop working till I hit the solution, but I do apologize for my tardy reply. This is my first post to this forum, and I must say I am very impressed. I'll do best to reply faster in the future. Your script was exactly what I was looking for.

    Thanks again,

    mikeyb

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Sooo...what's the real rpoblem?

    And how big is the database?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Feb 2005
    Posts
    10
    Well it looks like the real problem might be that the data I am after is not being stored in the database. I am not sure if this makes sense or not, but I was not able to find anything in the database using your script. I am playing phone tag with the tech support for the product trying to find out where the data I need gets stored.

    This is the scenario....the application that we are using allows for the creation of user defined fields to store additional information. When I create these fields in the front end I can see that for each new field I make a new record gets created in the database in the table CustomDataFields...so far so good....makes sense.

    However when I enter values into the front end for the user defined fields, I can not find the place in the db where the actual data gets stored. There is a field called CustomDataLists, that sounds very promising, but it contains no records, so that is not it... I am stumped.

    All I can think of is that this data is getting stored somewhere besides the sql server db, which is why I am now trying to contact the company.

    The data is definitely getting put somewhere...the front end knows where it goes, because when i close the front end and reopen it, the data I entered is still there, and in good order. I just need to know where to look, if not in the db.

    I've tried using the MS search tool in the app's directory structure in order to find a file that has my data in it, but no luck there either. If anyone has any brainstorms let me know.

    mikeyb

    p.s. the database is about 125 tables...only about a dozen tables are very beefy...the rest are just a few columns.
    Last edited by mikeyb; 02-10-05 at 10:27.

  10. #10
    Join Date
    Feb 2005
    Posts
    10

    Strange data type

    well....I found the data. It was in a poorly named table. I ended up finding it by using Brett's script in a way that shouldn't have worked.

    The first table i found while examining the schema was CustomDataFields.... seemed like a pretty nice thing, it was a table where records were created for each unique user created data field. There was another table called CustomDataLists where I was fully expecting to find the data that was entering into the additional fields i had defined through the front end. But it was blank, no records. But my data was getting stored somewhere.... searching for the values i had been entering into the fields i defined with your script produced nothing.


    I ran another search today on a whim for the title of one of the fields i had defined....something that should only have been listed in CustomDataFields and I got hits in a table called Plugin....

    So all the data is in there and one of the fields in the table is the title of the user defined field....talk about a horribly designed relational database. Plugin rendered CustomDataFields practically useless with 2 other repetitive fields.

    A record is created in Plugin for every time you hit ok in a particular window of the user interface. It has the effect of creating a new record every time a value to one of the fields is altered(except when one is completely deleted), from the first time a value is entered on. If a value is deleted or changed a field of its record is switched from null to the time the value was removed, if it was replaced by a new value instead of deleted a new record is created.

    It is simply bonkers, I'm young so I haven't had the chance to work closely with too many proprietary apps, but the back end of this one is very ugly, maybe that is something i will have to get used to.

    Anyway....the actual data put in the fields is stored in some kind of weird format, that is why your script wasn't detecting it. I can see the contents of it from inside the front end of a program called sql scripter...I have been using it lately as a ODBC db viewer

    ...but i haven't been able to get it to return from any kind of query yet. I need to dive into sql server data types, and/or find some kind of sql statement that will return the formatting info of a field.

    mikeyb

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Wierd format?

    What is the datatype?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Feb 2005
    Posts
    10

    problem solved

    the data type was text. I'm able to query it fine now, just had to use different operators. Thanks for everyone's help. Brett, once again that script was greatly appreciated, it led me to the solution. It would be nice to have it modified so it also searches fields with text data type using the LIKE operator. Maybe I'll go through it and make that change myself and repost it if I get some free time. If anyone else decides to do this before I get around to it please post it here, or post a link to it here. It is defintely a tool I would like to have at my disposal.

    Thanks again everyone,

    mikeyb

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I guess you could change the WHERE Clause to

    WHERE PATINDEX('%@SearchArg%', COLUMN_NAME) <> 0

    That would include text and ntext
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  14. #14
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  15. #15
    Join Date
    Feb 2005
    Posts
    10
    thanks again Brett, you da man.

    mikeyb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •