Trying to parameterize the database in a sql test script

Question

I'm trying to create a bunch of queries that can be used by my fellow testers that can be repeatable, and of course "parameter" driven.

One of my tests involves comparing the datatypes of one table against another, and I've tried to parameterize the query to the point that those using the query only need to amend the top area of the query, but I have the problem with one area, the database name.

I am not wanting to go down the Dynamic SQL route for this, but am looking for a way to get around the issue

    DECLARE @Source_DB      nvarchar(128) = 'SourceDB'
    ,       @Source_Table   nvarchar(128) = 'SourceTable'
    ,       @Source_Schema  nvarchar(128) = 'PJ';

    SELECT  *
    FROM    @Source_DB.INFORMATION_SCHEMA.COLUMNS isc  
    WHERE   isc.TABLE_NAME = @Source_Table AND isc.TABLE_SCHEMA = @Source_Schema

Full query below:

    DECLARE @Source_DB      nvarchar(128) = 'SourceDB'
    ,       @Source_Table   nvarchar(128) = 'SourceTable'
    ,       @Source_Schema  nvarchar(128) = 'SS'
    ,       @Target_DB      nvarchar(128) = 'TargetDB'
    ,       @Target_Table   nvarchar(128) = 'TargetTable'
    ,       @Target_Schema  nvarchar(128) = 'TS';

    SELECT  a.Source_TABLE_CATALOG
    ,       b.Target_TABLE_CATALOG  
    ,       a.Source_SCHEMA  
    ,       b.Target_SCHEMA  
    ,       a.Source_TABLE_NAME  
    ,       b.Target_TABLE_NAME  
    ,       a.Source_ORDINAL_POSITION  
    ,       b.Target_ORDINAL_POSITION  
    ,       a.Source_COLUMN_NAME  
    ,       b.Target_COLUMN_NAME  
    ,       a.Source_DATATYPE  
    ,       b.Target_DATATYPE  
    ,       a.Source_Nullable  
    ,       b.Target_Nullable  
    ,       a.Source_Default  
    ,       b.Target_Default
    FROM    (
            SELECT  isc.TABLE_CATALOG       AS [Source_TABLE_CATALOG]
            ,       isc.TABLE_SCHEMA        AS [Source_SCHEMA]  
            ,       isc.TABLE_NAME          AS [Source_TABLE_NAME]  
            ,       isc.ORDINAL_POSITION    AS [Source_ORDINAL_POSITION]  
            ,       isc.COLUMN_NAME         AS [Source_COLUMN_NAME]  
            ,       CASE 
                        WHEN isc.DATA_TYPE IN ('decimal', 'money', 'numeric') 
                            THEN CONCAT(isc.DATA_TYPE, '(', isc.NUMERIC_PRECISION, ',', isc.NUMERIC_SCALE, ')') 
                        WHEN isc.DATA_TYPE IN ('datetime','date','int') 
                            THEN isc.DATA_TYPE 
                        WHEN isc.DATETIME_PRECISION IS NOT NULL 
                            THEN CONCAT(isc.DATA_TYPE, '(', isc.DATETIME_PRECISION, ')') 
                        WHEN isc.DATA_TYPE = 'varchar' AND (isc.CHARACTER_MAXIMUM_LENGTH = 8000 OR isc.CHARACTER_MAXIMUM_LENGTH = -1) 
                            THEN 'varchar(MAX)' 
                        WHEN isc.DATA_TYPE = 'nvarchar' AND (isc.CHARACTER_MAXIMUM_LENGTH = 4000 OR isc.CHARACTER_MAXIMUM_LENGTH = -1) 
                            THEN 'nvarchar(MAX)' 
                        ELSE 
                            CONCAT(isc.DATA_TYPE, '(', isc.CHARACTER_MAXIMUM_LENGTH, ')') 
                    END AS [Source_DATATYPE]
            ,       CASE isc.IS_NULLABLE
                        WHEN 'YES' THEN 'NULL'
                        WHEN 'NO' THEN 'NOT NULL'
                    END AS [Source_Nullable]
            ,       isc.COLUMN_DEFAULT AS [Source_Default]
            FROM    @Source_DB.INFORMATION_SCHEMA.COLUMNS isc  
            WHERE   isc.TABLE_NAME = @Source_Table AND isc.TABLE_SCHEMA = @Source_Schema
            ) a
            JOIN    (
                    SELECT  isc.TABLE_CATALOG       AS [Target_TABLE_CATALOG]
                    ,       isc.TABLE_SCHEMA        AS [Target_SCHEMA]  
                    ,       isc.TABLE_NAME          AS [Target_TABLE_NAME]  
                    ,       isc.ORDINAL_POSITION    AS [Target_ORDINAL_POSITION]  
                    ,       isc.COLUMN_NAME         AS [Target_COLUMN_NAME]  
                    ,       CASE
                                WHEN isc.DATA_TYPE IN ('decimal', 'money', 'numeric')
                                    THEN CONCAT(isc.DATA_TYPE, '(', isc.NUMERIC_PRECISION, ',', isc.NUMERIC_SCALE, ')')
                                WHEN isc.DATA_TYPE IN ('datetime','date','int')
                                    THEN isc.DATA_TYPE
                                WHEN isc.DATETIME_PRECISION IS NOT NULL
                                    THEN CONCAT(isc.DATA_TYPE, '(', isc.DATETIME_PRECISION, ')')
                                WHEN isc.DATA_TYPE = 'varchar' AND (isc.CHARACTER_MAXIMUM_LENGTH = 8000 OR isc.CHARACTER_MAXIMUM_LENGTH = -1) 
                                    THEN 'varchar(MAX)'
                                WHEN isc.DATA_TYPE = 'nvarchar' AND (isc.CHARACTER_MAXIMUM_LENGTH = 4000 OR isc.CHARACTER_MAXIMUM_LENGTH = -1) 
                                    THEN 'nvarchar(MAX)'
                                ELSE
                                    CONCAT(isc.DATA_TYPE, '(', isc.CHARACTER_MAXIMUM_LENGTH, ')')
                            END AS [Target_DATATYPE]
                    ,       CASE isc.IS_NULLABLE
                                WHEN 'YES' THEN 'NULL'
                                WHEN 'NO' THEN 'NOT NULL'
                            END AS [Target_Nullable]
                    ,       isc.COLUMN_DEFAULT      AS [Target_Default]
                    FROM    @Target_DB.INFORMATION_SCHEMA.COLUMNS isc
                    WHERE   isc.TABLE_NAME = @Target_Table AND isc.TABLE_SCHEMA = @Target_Schema 
                    ) b ON  a.Source_COLUMN_NAME = b.Target_COLUMN_NAME
    WHERE   a.Source_DATATYPE <> b.Target_DATATYPE OR
            a.Source_Nullable <> b.Target_Nullable OR
            a.Source_Default <> b.Target_Default

Ideal outcome is that I can get the SQL query to work, but It may prove that this is something that cannot be done outside of a dynamic query, which wouldn't cause an issue from my point as the code would be held in a text file (with all the other templated queries), and would not run until the parameters were entered.

"I am not wanting to go down the Dynamic SQL route for this, but am looking for a way to get around the issue" You can't not. You cannot replace the name of an object with an expression, or parameter/variable. They must be literal values. SELECT * FROM @DBName.dbo.MyTable; will generate an error Incorrect syntax near '.'. — Thom A
– Thom A ♦, Commented Sep 12, 2019 at 10:24
If you are using that duplicate, look at the example using QUOTENAME (and is upvoted), not the accepted answer. — Thom A
– Thom A ♦, Commented Sep 12, 2019 at 10:27
There's another solution for this particular case that involves creating a cross-database view with UNION ALL of their relevant information schema columns. You will have to maintain the view on the creation or drop of each database (with a server-level trigger). — EzLo
– EzLo, Commented Sep 12, 2019 at 10:28
There is a solution without dynamic SQL using SQLCMD scripting variables. Unfortunately, this question was closed as a dup so I can't provide the answer. — Dan Guzman
– Dan Guzman, Commented Sep 12, 2019 at 10:32

Dan Guzman · Accepted Answer · 2019-09-12 10:46:16Z

Identifiers in T-SQL statements cannot be parameterized. However, you can use SQLCMD scripting variables so that the variables are replaced before the T-SQL query is compiled and executed, functionally like a text search/replace.

The example below uses SQLCMD scripting variables instead of T-SQL variables/parameters. Scripts with SQLCMD variables can be run from SSMS in SQLCMD mode (Query-->Options-->SQLCMD Mode) or via the SQLCMD command-line utility.

:SETVAR SOURCE_DB YourSourceDatabase
:SETVAR TARGET_DB YourTargetDatabase

DECLARE
            @Source_Table   nvarchar(128) = 'SourceTable'
    ,       @Source_Schema  nvarchar(128) = 'SS'
    ,       @Target_Table   nvarchar(128) = 'TargetTable'
    ,       @Target_Schema  nvarchar(128) = 'TS';

    SELECT  a.Source_TABLE_CATALOG
    ,       b.Target_TABLE_CATALOG  
    ,       a.Source_SCHEMA  
    ,       b.Target_SCHEMA  
    ,       a.Source_TABLE_NAME  
    ,       b.Target_TABLE_NAME  
    ,       a.Source_ORDINAL_POSITION  
    ,       b.Target_ORDINAL_POSITION  
    ,       a.Source_COLUMN_NAME  
    ,       b.Target_COLUMN_NAME  
    ,       a.Source_DATATYPE  
    ,       b.Target_DATATYPE  
    ,       a.Source_Nullable  
    ,       b.Target_Nullable  
    ,       a.Source_Default  
    ,       b.Target_Default
    FROM    (
            SELECT  isc.TABLE_CATALOG       AS [Source_TABLE_CATALOG]
            ,       isc.TABLE_SCHEMA        AS [Source_SCHEMA]  
            ,       isc.TABLE_NAME          AS [Source_TABLE_NAME]  
            ,       isc.ORDINAL_POSITION    AS [Source_ORDINAL_POSITION]  
            ,       isc.COLUMN_NAME         AS [Source_COLUMN_NAME]  
            ,       CASE 
                        WHEN isc.DATA_TYPE IN ('decimal', 'money', 'numeric') 
                            THEN CONCAT(isc.DATA_TYPE, '(', isc.NUMERIC_PRECISION, ',', isc.NUMERIC_SCALE, ')') 
                        WHEN isc.DATA_TYPE IN ('datetime','date','int') 
                            THEN isc.DATA_TYPE 
                        WHEN isc.DATETIME_PRECISION IS NOT NULL 
                            THEN CONCAT(isc.DATA_TYPE, '(', isc.DATETIME_PRECISION, ')') 
                        WHEN isc.DATA_TYPE = 'varchar' AND (isc.CHARACTER_MAXIMUM_LENGTH = 8000 OR isc.CHARACTER_MAXIMUM_LENGTH = -1) 
                            THEN 'varchar(MAX)' 
                        WHEN isc.DATA_TYPE = 'nvarchar' AND (isc.CHARACTER_MAXIMUM_LENGTH = 4000 OR isc.CHARACTER_MAXIMUM_LENGTH = -1) 
                            THEN 'nvarchar(MAX)' 
                        ELSE 
                            CONCAT(isc.DATA_TYPE, '(', isc.CHARACTER_MAXIMUM_LENGTH, ')') 
                    END AS [Source_DATATYPE]
            ,       CASE isc.IS_NULLABLE
                        WHEN 'YES' THEN 'NULL'
                        WHEN 'NO' THEN 'NOT NULL'
                    END AS [Source_Nullable]
            ,       isc.COLUMN_DEFAULT AS [Source_Default]
            FROM    [$(SOURCE_DB)].INFORMATION_SCHEMA.COLUMNS isc  
            WHERE   isc.TABLE_NAME = @Source_Table AND isc.TABLE_SCHEMA = @Source_Schema
            ) a
            JOIN    (
                    SELECT  isc.TABLE_CATALOG       AS [Target_TABLE_CATALOG]
                    ,       isc.TABLE_SCHEMA        AS [Target_SCHEMA]  
                    ,       isc.TABLE_NAME          AS [Target_TABLE_NAME]  
                    ,       isc.ORDINAL_POSITION    AS [Target_ORDINAL_POSITION]  
                    ,       isc.COLUMN_NAME         AS [Target_COLUMN_NAME]  
                    ,       CASE
                                WHEN isc.DATA_TYPE IN ('decimal', 'money', 'numeric')
                                    THEN CONCAT(isc.DATA_TYPE, '(', isc.NUMERIC_PRECISION, ',', isc.NUMERIC_SCALE, ')')
                                WHEN isc.DATA_TYPE IN ('datetime','date','int')
                                    THEN isc.DATA_TYPE
                                WHEN isc.DATETIME_PRECISION IS NOT NULL
                                    THEN CONCAT(isc.DATA_TYPE, '(', isc.DATETIME_PRECISION, ')')
                                WHEN isc.DATA_TYPE = 'varchar' AND (isc.CHARACTER_MAXIMUM_LENGTH = 8000 OR isc.CHARACTER_MAXIMUM_LENGTH = -1) 
                                    THEN 'varchar(MAX)'
                                WHEN isc.DATA_TYPE = 'nvarchar' AND (isc.CHARACTER_MAXIMUM_LENGTH = 4000 OR isc.CHARACTER_MAXIMUM_LENGTH = -1) 
                                    THEN 'nvarchar(MAX)'
                                ELSE
                                    CONCAT(isc.DATA_TYPE, '(', isc.CHARACTER_MAXIMUM_LENGTH, ')')
                            END AS [Target_DATATYPE]
                    ,       CASE isc.IS_NULLABLE
                                WHEN 'YES' THEN 'NULL'
                                WHEN 'NO' THEN 'NOT NULL'
                            END AS [Target_Nullable]
                    ,       isc.COLUMN_DEFAULT      AS [Target_Default]
                    FROM    [$(TARGET_DB)].INFORMATION_SCHEMA.COLUMNS isc
                    WHERE   isc.TABLE_NAME = @Target_Table AND isc.TABLE_SCHEMA = @Target_Schema 
                    ) b ON  a.Source_COLUMN_NAME = b.Target_COLUMN_NAME
    WHERE   a.Source_DATATYPE <> b.Target_DATATYPE OR
            a.Source_Nullable <> b.Target_Nullable OR
            a.Source_Default <> b.Target_Default;

With the SQLCMD utility, you can alternatively use -v arguments to pass the desired values on the command line instead of in the script itself.

This is perfect. It is only ever going to be run by a tester, and won't be held in any "code" library, but is going to be utilised by testers as a set of tests they can run on each BI piece they work on. Because of this, I doubt there would be any need to worry about SQL Injection occurring, as once it's run, its run. Nothing else is holding the code.
@ZoharPeled, no, this is not SQL injection safe. But note that one can run any ad-hoc query from SQLCMD or SSMS anyway, limited only by the user's permissions, so injection is not a concern. This assumes one isn't invoking SQLCMD from app code for end user functionality, in which case SQL injection is a concern and this method should not be used.
@ZoharPeled, you're correct there. No worries about any SQL Injection as its only run by the testers.

Collectives™ on Stack Overflow

Trying to parameterize the database in a sql test script

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related