Where Did They Put the Data?

I can’t be the only one that is presented with a database I have never seen and asked if I can build a few reports based off of the data.  Sometimes there are no database diagrams, documentation or artifacts I can use to help get me started.

Recently I was asked to write a report that displayed the information that a user had submitted on a web form.   I had access to the SQL Server database so I wrote a stored procedure to loop through all the tables and locate a phrase that one of the users had submitted into the system.   Once I found the phrase, I could figure out how to build a report that included it and included the other data the user submitted.   You will find the procedure at the end of the article.
Step 1, looping through tables
Since my requirements for searching did not include fast searches, and the tables were thousands and not millions of records long, I chose to use a cursor to solve the looping problem.
DECLARE crsTable CURSOR FOR
SELECT
      name = ‘[‘ + ss.name + ‘].[‘ + so.name + ‘]’
      ,id = so.object_id
FROM
      sys.objects so
      INNER JOIN
      sys.schemas ss
      ON so.schema_id = ss.schema_id
WHERE
      so.Type=‘U’
      AND so.name <> ‘dtproperties’
ORDER BY
      name
Step 2, another cursor to get the columns for each table
I needed to get each column so that I could search it using a where clause.
      DECLARE crsField CURSOR FOR
      SELECT
            name
      FROM
            syscolumns
      WHERE
            id =@intID
      ORDER BY
            colorder
Step 3, Build the dynamic SQL for the WHERE clause
For each of the fields, assemble the where clause that will be used to locate the data.  I chose to use charindex and to make it fast to code the routine, I chose to convert fields to text and then search.  Searches would be much faster if I took into account the data types and searched without converting everything to text, but I was creating this fast so this seemed to me to be the quickest implementation.  I added the CRLF { CHAR(13) + CHAR(10) } to make it easier to read the SQL in the messages window of Query Analyzer.
SET  @strSQL =
     @strSQL
     + ‘CHARINDEX(”’ 
     + REPLACE(@strSearch ,””, ”””) 
     + ”’,CONVERT(varchar(max),[‘ 
     + @strField + ‘] )) > 0’ 
     + CHAR(13 ) 
     + CHAR( 10)
Step 4, Execute the dynamic SQL
Execute the SQL to find the records where the data is located.
EXECUTE (@strSQL )
Final Notes
Of course, I added the error management so the process would continue in case of an error.  In fact, if you run this against the SQL Server AdventureWorks2008 database you will receive one.  There is an XML column that contains data that is defined with a collation that is not the default for the database.   Perhaps in a future article I will take a look at some ways to address this.
When you execute the procedure from the query window, you should view the Messages tab to see what errors may have occurred.  If an error occurred during EXECUTE, the SQL is included in the Messages tab so you can cut and paste it into a query window, make appropriate changes, and execute it.
SQL Procedure
Here is the SQL Procedure in it’s entirety.
ALTER PROCEDURE prcSearchForText
(
       @strSearch varchar(1000)
      ,@blnDisplayAll bit = 0
)
AS
— Example: EXECUTE prcSearchForText ‘frame’
— Example: EXECUTE prcSearchForText ‘frame’,1
SET NOCOUNT ON
DECLARE @strTable varchar (1000)
DECLARE @intID int
DECLARE @strField varchar (1000)
DECLARE @strSQL         varchar( 8000)
SET @strSearch = ‘frame’
DECLARE crsTable CURSOR FOR
SELECT
      name = ‘[‘ + ss.name + ‘].[‘ + so.name + ‘]’
      ,id = so.object_id
FROM
      sys.objects so
INNER JOIN
      sys.schemas ss
      ON so.schema_id = ss.schema_id
WHERE
      so.Type=‘U’
      AND so.name <> ‘dtproperties’
ORDER BY
      name
OPEN crsTable
FETCH NEXT FROM crsTable INTO @strTable,@intID
WHILE @@FETCH_STATUS <> 1
BEGIN
      SET @strSQL = 
      
      DECLARE crsField CURSOR FOR
      SELECT
            name
      FROM
            syscolumns
      WHERE
            id =@intID
      ORDER BY
            colorder
            
      OPEN crsField
      FETCH NEXT FROM crsField INTO @strField
      WHILE @@FETCH_STATUS <> 1
      BEGIN
            IF @strSQL <> 
                  SET @strSQL = @strSQL + ‘OR ‘
            SET @strSQL = @strSQL
               + ‘CHARINDEX(”’ + REPLACE(@strSearch ,””, ”””)
               + ”’,CONVERT(varchar(max),[‘ + @strField + ‘] )) > 0’ 
               + CHAR(13 ) + CHAR( 10)
            FETCH NEXT FROM crsField INTO @strField
      END
      CLOSE crsField
      DEALLOCATE crsField
      SET @strSQL = ‘SELECT * FROM ‘ + @strTable + ‘ ‘ + CHAR (13) + CHAR(10 ) + ‘WHERE ‘ + @strSQL
      IF @blnDisplayAll = 1
      BEGIN
            PRINT ‘—————————————-‘
            PRINT @strTable
            PRINT @strSQL
      END
      
      BEGIN TRY
            EXECUTE(@strSQL )
      END TRY
      BEGIN CATCH
            IF @blnDisplayAll = 0
            BEGIN
                  PRINT ‘—————————————-‘
                  PRINT @strTable
                  PRINT @strSQL
            END
            SET @strSQL
            = ‘   ERROR_NUMBER: ‘    + ISNULL(CONVERT (varchar( 50),ERROR_NUMBER ()),)
            + ‘   ERROR_SEVERITY: ‘  + ISNULL(CONVERT (varchar( 50),ERROR_SEVERITY ()),)
            + ‘   ERROR_STATE: ‘     + ISNULL(CONVERT (varchar( 50),ERROR_STATE ()),)
            + ‘   ERROR_PROCEDURE: ‘ + ISNULL(ERROR_PROCEDURE (),)
            + ‘   ERROR_LINE: ‘      + ISNULL(CONVERT (varchar( 50),ERROR_LINE ()),)
            + ‘   ERROR_MESSAGE: ‘   + ISNULL(ERROR_MESSAGE (),)
            PRINT @strSQL
      END CATCH
      FETCH NEXT FROM crsTable INTO @strTable,@intID
END
CLOSE crsTable
DEALLOCATE crsTable
SET NOCOUNT OFF 
GO
  • Posting Calendar

    December 2017
    M T W T F S S
    « Mar    
     123
    45678910
    11121314151617
    18192021222324
    25262728293031