Tag Archives: OLTP

Free download: Blog Yearbook 2016

I have made a compilation of all my blog posts of the past year, and it was more work than you would think.
Especially because it also contains a non-published new article.

After filling in the form you can download the e-book for free.

    I promise you the following:

    • I will not give or sell your email address to any third party.
    • I will not spam you.
    • If I start with a newsletter, you can easily unsubscribe or change the frequency.

    (c) 2016 hansmichiels.com – Do not steal the contents – spread the link instead – thank you.

    When reverse engineering is a necessary evil .. (SQL Server Series)

    .. or: How to find data (text, guids, numbers) in your entire SQL Server database ..

    Introduction

    A number of years ago I had to make queries on an OLTP database used by third parties’ software. As a person preferring data warehouses for too many reasons to mention here, I am not proud of it, but sometimes these requests just happen. If you have the same now and then, you are not the only one 😉 .

    Problem

    A little simplified, this is what happened:
    I started asking around, is there documentation? “No”, there wasn’t.
    Is there a data dictionary? “No”, there wasn’t, either.
    Can I have access to the database? “Yes, here’s the connection string.”
    So I made a connection, but .. what do all these tables mean? How are they related? How are the foreign keys? The model didn’t tell me, with no physical constraints and unclear table- and column names.
    Do you have anything else for me to see how this works? “Well, we have a PROD and a TEST environment, you can have read only access to PROD and read/write to the TEST.”
    Okay, that is something ..

    MSSQL-S01E02-testconnection
    Picture credits: © David Coleman | Dreamstime Stock Photos
    When there is only a (test)connection..

    Towards a solution

    Could you show me how you use the system? Luckily the employee of my client was very helpful and showed me how he used the application.
    I tried to get a grip on the SQL Statements caused by his actions, but due to a lack of permissions on the database, this didn’t work.
    This could be arranged, but could take a while before I actually would have temporary “elevated” permissions.
    As it was just some small piece of “inbetween” work, that seemed an inefficient route to follow.
    Okay, I have seen what you did, can I do that on the TEST as well? “Yeah, go ahead.”
    So I started to make an order, using silly names and descriptions, and choosing weird seldomly used products. So, what’s next?
    Ok, if I can find where my description “First order of Hans” is stored, I can start to map the frontend to the backend, or make clear for myself where the entered information is stored in the database.

    Solution

    This is where this script became useful. It finds all occurrences of one or more text values in a SQL Server database.

    WARNING: Do not use in production environments, as this script can potentially run pretty long (10 to 20 minutes when I used it) and can have a negative impact on database performance in general (so other users might regret that you are running the script).
    So use it in a test enviroment when you can.

    SearchTextInDatabase.sql

    --\
    ---) SearchTextInDatabase script.
    ---) Author: Hans Michiels
    ---) Searches for one or more texts in all (n)(var)char and (n)text
    ---) columns of a SQL Server database.
    ---) WARNING: Can potentially run very long, my advise is not
    ---) to run this script in production environments!
    --/
    /*
    (c) Copyright 2016 - hansmichiels.com
     
    This program is free software: you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
    the Free Software Foundation, either version 3 of the License, or
    (at your option) any later version.
     
    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
    GNU General Public License for more details.
     
    You should have received a copy of the GNU General Public License
    along with this program. If not, see http://www.gnu.org/licenses/.
    */

    USE [AdventureWorks2014]
    GO

    --\-----------------------------------------------------------------------------------
    ---) DECLARATIONS
    --/-----------------------------------------------------------------------------------
    DECLARE @schema_name SYSNAME
    DECLARE @table_name SYSNAME
    DECLARE @column_name SYSNAME
    DECLARE @nsql NVARCHAR(4000)
    DECLARE @param_def NVARCHAR(500)
    DECLARE @search_text NVARCHAR(1000)
    DECLARE @search_texts TABLE ([search_text] NVARCHAR(1000))
    DECLARE @debug_print NVARCHAR(500)
    DECLARE @results_count INT = 0

    IF OBJECT_ID('tempdb..#search_results') IS NOT NULL BEGIN
        DROP TABLE #search_results
    END
    CREATE TABLE #search_results
        ( [search_text] NVARCHAR(1000)
        , [schema_name] NVARCHAR(128)
        , [table_name] NVARCHAR(128)
        , [column_name] NVARCHAR(128)
        , [the_value] NVARCHAR(1000)
        )

    DECLARE columns_cursor CURSOR LOCAL STATIC FOR
        SELECT sch.name AS [schema_name]
        , tbl.name AS [table_name]
        , col.name AS [column_name]
        FROM sys.tables tbl
         JOIN sys.schemas sch
            ON sch.schema_id = tbl.schema_id
          JOIN sys.columns col
            ON col.object_id = tbl.object_id
        WHERE col.system_type_id IN (175, 167, 231, 239, 35, 99) -- (N)(VAR)CHAR, (N)TEXT

    --\-----------------------------------------------------------------------------------
    ---) INITIALIZATION
    ---) Specify the texts to search for below.
    ---) Use the wildcard % if you want to do a 'like' search.
    --/-----------------------------------------------------------------------------------
    INSERT INTO @search_texts

      SELECT '%Assembly%'
      UNION SELECT 'First order of Hans'

    DECLARE search_cursor CURSOR LOCAL STATIC FOR
        SELECT search_text
        FROM @search_texts

    --\-----------------------------------------------------------------------------------
    ---) MAIN
    --/-----------------------------------------------------------------------------------
    OPEN columns_cursor
    FETCH NEXT FROM columns_cursor INTO @schema_name, @table_name, @column_name

    WHILE @@FETCH_STATUS = 0
    BEGIN

        SET @debug_print = 'Processing ' + @table_name + '.' + @column_name;
        PRINT REPLICATE('-', LEN(@debug_print))
        PRINT @debug_print
        PRINT REPLICATE('-', LEN(@debug_print))
        
        SET @nsql = 'INSERT INTO #search_results SELECT DISTINCT @inner_search_text, '
            + '''' + @schema_name + ''' AS [schema_name], '
            + '''' + @table_name + ''' AS [table_name], '
            + '''' + @column_name + ''' AS [column_name], CONVERT(NVARCHAR(1000), ' + QUOTENAME(@column_name) + ') AS [the_value] '
            + ' FROM ' + QUOTENAME(@schema_name) + '.' + QUOTENAME(@table_name) + ' WITH (NOLOCK) '
            + ' WHERE ' + QUOTENAME(@column_name) + ' LIKE @inner_search_text;';

        OPEN search_cursor
        FETCH NEXT FROM search_cursor INTO @search_text

        WHILE @@FETCH_STATUS = 0
        BEGIN
            PRINT ''
            PRINT '--> Processing search text ''' + @search_text + '''';
            SET @param_def = N'@inner_search_text NVARCHAR(1000)';
            EXECUTE sp_executesql @nsql, @param_def,
                                  @inner_search_text = @search_text;

            FETCH NEXT FROM search_cursor INTO @search_text
        END
        
        CLOSE search_cursor

        FETCH NEXT FROM columns_cursor INTO @schema_name, @table_name, @column_name
        
    END

    CLOSE columns_cursor
    DEALLOCATE columns_cursor

    SELECT
          t.schema_name
        , t.table_name
        , t.column_name
        , t.the_value
        , 'SELECT * FROM ' + QUOTENAME(t.schema_name) + '.' + QUOTENAME(t.table_name)
          + ' WHERE ' + QUOTENAME(t.column_name) + ' = ''' + REPLACE(t.the_value, '''', '''''') + '''' AS [select_statement]
     FROM #search_results t

    GO

    Some time later, I also made two similar versions for searching unique identifiers and numeric values.
    I know the scripts might not look so ‘pretty’, but they do the job when needed.

    SearchUniqueIdentifierInDatabase.sql

    --\
    ---) SearchUniqueIdentifierInDatabase script.
    ---) Author: Hans Michiels
    ---) Searches for one or more unique identifiers in all UNIQUEIDENTIFIER columns
    ---) of a SQL Server database.
    ---) WARNING: Can potentially run very long, my advise is not
    ---) to run this script in production environments!
    --/
    /*
    (c) Copyright 2016 - hansmichiels.com
     
    This program is free software: you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
    the Free Software Foundation, either version 3 of the License, or
    (at your option) any later version.
     
    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
    GNU General Public License for more details.
     
    You should have received a copy of the GNU General Public License
    along with this program. If not, see http://www.gnu.org/licenses/.
    */

    USE [AdventureWorks2014]
    GO

    --\-----------------------------------------------------------------------------------
    ---) DECLARATIONS
    --/-----------------------------------------------------------------------------------
    DECLARE @schema_name SYSNAME
    DECLARE @table_name SYSNAME
    DECLARE @column_name SYSNAME
    DECLARE @nsql NVARCHAR(4000)
    DECLARE @param_def NVARCHAR(500)
    DECLARE @search_value UNIQUEIDENTIFIER
    DECLARE @search_values TABLE ([search_value] UNIQUEIDENTIFIER)
    DECLARE @debug_print NVARCHAR(500)
    DECLARE @results_count INT = 0

    IF OBJECT_ID('tempdb..#search_results') IS NOT NULL BEGIN
        DROP TABLE #search_results
    END
    CREATE TABLE #search_results
        ( [search_value] UNIQUEIDENTIFIER
        , [schema_name] NVARCHAR(128)
        , [table_name] NVARCHAR(128)
        , [column_name] NVARCHAR(128)
        , [the_value] UNIQUEIDENTIFIER
        )

    DECLARE columns_cursor CURSOR LOCAL STATIC FOR
        SELECT sch.name AS [schema_name]
        , tbl.name AS [table_name]
        , col.name AS [column_name]
        FROM sys.tables tbl
         JOIN sys.schemas sch
            ON sch.schema_id = tbl.schema_id
          JOIN sys.columns col
            ON col.object_id = tbl.object_id
                                  -- UNIQUEIDENTIFIER
        WHERE col.system_type_id IN (36)

    --\-----------------------------------------------------------------------------------
    ---) INITIALIZATION
    ---) Specify the unique identifiers to search for below.
    --/-----------------------------------------------------------------------------------
    INSERT INTO @search_values
    SELECT 'D081136E-38D6-4D42-8FFD-19A6A8FA61E6'
    UNION SELECT '9AADCB0D-36CF-483F-84D8-585C2D4EC6E9'
    UNION SELECT '32A54B9E-E034-4BFB-B573-A71CDE60D8C0'
    UNION SELECT '4C506923-6D1B-452C-A07C-BAA6F5B142A4'

    DECLARE search_cursor CURSOR LOCAL STATIC FOR
        SELECT search_value
        FROM @search_values

    --\-----------------------------------------------------------------------------------
    ---) MAIN
    --/-----------------------------------------------------------------------------------
    OPEN columns_cursor
    FETCH NEXT FROM columns_cursor INTO @schema_name, @table_name, @column_name

    WHILE @@FETCH_STATUS = 0
    BEGIN

        SET @debug_print = 'Processing ' + @table_name + '.' + @column_name;
        PRINT REPLICATE('-', LEN(@debug_print))
        PRINT @debug_print
        PRINT REPLICATE('-', LEN(@debug_print))
        
        SET @nsql = 'INSERT INTO #search_results SELECT DISTINCT @inner_search_value, '
            + '''' + @schema_name + ''' AS [schema_name], '
            + '''' + @table_name + ''' AS [table_name], '
            + '''' + @column_name + ''' AS [column_name], ' + QUOTENAME(@column_name) + ' AS [the_value] '
            + ' FROM ' + QUOTENAME(@schema_name) + '.' + QUOTENAME(@table_name) + ' WITH (NOLOCK) '
            + ' WHERE ' + QUOTENAME(@column_name) + ' = @inner_search_value ;';

        OPEN search_cursor
        FETCH NEXT FROM search_cursor INTO @search_value

        WHILE @@FETCH_STATUS = 0
        BEGIN
            PRINT ''
            PRINT '--> Processing search value ''' + CONVERT(NVARCHAR(40), @search_value) + '''';
            SET @param_def = N'@inner_search_value UNIQUEIDENTIFIER';
            EXECUTE sp_executesql @nsql, @param_def,
                                  @inner_search_value = @search_value;

            FETCH NEXT FROM search_cursor INTO @search_value
        END
        
        CLOSE search_cursor

        FETCH NEXT FROM columns_cursor INTO @schema_name, @table_name, @column_name
        
        SELECT @results_count = (SELECT COUNT(*) FROM #search_results);
        
    END

    CLOSE columns_cursor
    DEALLOCATE columns_cursor

    SELECT
          t.schema_name
        , t.table_name
        , t.column_name
        , t.the_value
        , 'SELECT * FROM ' + QUOTENAME(t.schema_name) + '.' + QUOTENAME(t.table_name)
          + ' WHERE ' + QUOTENAME(t.column_name) + ' = ''' + REPLACE(t.the_value, '''', '''''') + '''' AS [select_statement]
     FROM #search_results t

    GO


    SearchNumberInDatabase.sql

    --\
    ---) SearchNumberInDatabase script.
    ---) Author: Hans Michiels
    ---) Searches for one or more numeric values in all TINYINT, SMALLINT,
    ---) INT, FLOAT, DECIMAL and NUMERIC columns of a SQL Server database.
    ---) WARNING: Can potentially run very long, my advise is not
    ---) to run this script in production environments!
    --/
    /*
    (c) Copyright 2016 - hansmichiels.com
     
    This program is free software: you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
    the Free Software Foundation, either version 3 of the License, or
    (at your option) any later version.
     
    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
    GNU General Public License for more details.
     
    You should have received a copy of the GNU General Public License
    along with this program. If not, see http://www.gnu.org/licenses/.
    */

    USE [AdventureWorks2014]
    GO

    --\-----------------------------------------------------------------------------------
    ---) DECLARATIONS
    --/-----------------------------------------------------------------------------------
    DECLARE @schema_name SYSNAME
    DECLARE @table_name SYSNAME
    DECLARE @column_name SYSNAME
    DECLARE @nsql NVARCHAR(4000)
    DECLARE @param_def NVARCHAR(500)
    DECLARE @search_value DECIMAL(38, 2)
    DECLARE @search_values TABLE ([search_value] DECIMAL(38, 2))
    DECLARE @debug_print NVARCHAR(500)
    DECLARE @results_count INT = 0
    DECLARE @max_difference DECIMAL(38, 2) -- Use to find values 'near by'

    IF OBJECT_ID('tempdb..#search_results') IS NOT NULL BEGIN
        DROP TABLE #search_results
    END
    CREATE TABLE #search_results
        ( [search_value] DECIMAL(38, 2)
        , [schema_name] NVARCHAR(128)
        , [table_name] NVARCHAR(128)
        , [column_name] NVARCHAR(128)
        , [the_value] DECIMAL(38, 2)
        )

    DECLARE columns_cursor CURSOR LOCAL STATIC FOR
        SELECT sch.name AS [schema_name]
        , tbl.name AS [table_name]
        , col.name AS [column_name]
        FROM sys.tables tbl
         JOIN sys.schemas sch
            ON sch.schema_id = tbl.schema_id
          JOIN sys.columns col
            ON col.object_id = tbl.object_id
                                  -- TINYINT, SMALLINT, INT, INT
        WHERE col.system_type_id IN (48, 52, 56, 127
                                  -- FLOAT, DECIMAL, FLOAT, DECIMAL, DECIMAL, DECIMAL
                                  --, 59, 60, 62, 106, 108, 122, 127
                                    )

    --\-----------------------------------------------------------------------------------
    ---) INITIALIZATION
    ---) Specify the texts to search for below “INSERT INTO @search_values”
    ---) Also set a value for @max_difference
    ---) @max_difference makes it possible to find nearby numbers.
    --/-----------------------------------------------------------------------------------
    SELECT @max_difference = 1.0
    INSERT INTO @search_values
      SELECT 755
      --UNION SELECT 97867563

    DECLARE search_cursor CURSOR LOCAL STATIC FOR
        SELECT search_value
        FROM @search_values

    -------------------------------------------------------------------------------------
    -- MAIN
    -------------------------------------------------------------------------------------
    OPEN columns_cursor
    FETCH NEXT FROM columns_cursor INTO @schema_name, @table_name, @column_name

    WHILE @@FETCH_STATUS = 0
    BEGIN

        SET @debug_print = 'Processing ' + @table_name + '.' + @column_name;
        PRINT REPLICATE('-', LEN(@debug_print))
        PRINT @debug_print
        PRINT REPLICATE('-', LEN(@debug_print))
        
        SET @nsql = 'INSERT INTO #search_results SELECT DISTINCT @inner_search_value, '
            + '''' + @schema_name + ''' AS [schema_name], '
            + '''' + @table_name + ''' AS [table_name], '
            + '''' + @column_name + ''' AS [column_name], CONVERT(DECIMAL(38, 2), ' + QUOTENAME(@column_name) + ') AS [the_value] '
            + ' FROM ' + QUOTENAME(@schema_name) + '.' + QUOTENAME(@table_name) + ' WITH (NOLOCK) WHERE ABS(' + QUOTENAME(@column_name) + ' - @inner_search_value) <= ' + CAST(@max_difference AS VARCHAR(50)) + ';';

        PRINT @nsql
        
        OPEN search_cursor
        FETCH NEXT FROM search_cursor INTO @search_value

        WHILE @@FETCH_STATUS = 0
        BEGIN
            PRINT ''
            PRINT '--> Processing search value ''' + CONVERT(NVARCHAR(40), @search_value) + '''';
            SET @param_def = N'@inner_search_value DECIMAL(38, 2)';
            EXECUTE sp_executesql @nsql, @param_def,
                                  @inner_search_value = @search_value;

            FETCH NEXT FROM search_cursor INTO @search_value
        END
        
        CLOSE search_cursor

        FETCH NEXT FROM columns_cursor INTO @schema_name, @table_name, @column_name
        
        SELECT @results_count = (SELECT COUNT(*) FROM #search_results);
        
    END

    CLOSE columns_cursor
    DEALLOCATE columns_cursor

    SELECT
          t.schema_name
        , t.table_name
        , t.column_name
        , t.the_value
        , 'SELECT * FROM ' + QUOTENAME(t.schema_name) + '.' + QUOTENAME(t.table_name) + ' WHERE ' + QUOTENAME(t.column_name) + ' = ''' + REPLACE(t.the_value, '''', '''''') + ''''
     FROM #search_results t

    GO

    Download all scripts here.

    Conclusion / Wrap up

    When you have to understand a SQL Server datamodel, for instance to get data out, and there is no documentation but only a test environment and database connection available, these scripts can help you to find where data entered in the frontend software is stored in the database.
    A different use case is when the database is using unique identifiers for primary and foreign keys, and the foreign key relationships are not physically enforced and/or flexible. In that case you can search for a specific unique identifier and see where it is used in the database.

    (c) 2016 hansmichiels.com – Do not steal the contents – spread the link instead – thank you.