SQL Test forum

Welcome to the SQL Test feature suggestion list. Find out more information about SQL Test at http://www.red-gate.com/products/sql-development/sql-test/.

If you have any questions, need help or have found a bug in SQL Test, please visit the forums at http://www.red-gate.com/messageboard/viewforum.php?f=161.

How can we improve SQL Test?

You've used all your votes and won't be able to post a new idea, but you can still search and comment on existing ideas.

There are two ways to get more votes:

  • When an admin closes an idea you've voted on, you'll get your votes back from that idea.
  • You can remove your votes from an open idea you support.
  • To see ideas you have already voted on, select the "My feedback" filter and select "My open ideas".
(thinking…)

Enter your idea and we'll search to see if someone has already suggested it.

If a similar idea already exists, you can vote and comment on it.

If it doesn't exist, you can post your idea so others can vote on it.

Enter your idea and we'll search to see if someone has already suggested it.

  1. Option for actual results to be shown as INSERT statements

    It would be great if there was an option to have the actual results in the SQL Test Messages window appear as INSERT statements so that I can just copy and paste them into my test case to populate the "expected" table.

    I have a limited set of test data in my SetUp stored procedure, so I know the expected results are accurate. Right now I have to copy and paste each field's value into an appropriate insert statement in my test case, and this is very laborious. This could very easily be automated.

    3 votes
    Vote
    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      I agree to the terms of service
      Signed in as (Sign out)
      You have left! (?) (thinking…)
      1 comment  ·  Admin →
    • Ctrl+Tab shortcut doesn't work in SQL Test window

      The Ctrl+Tab shortcut key doesn't work to switch back to another tab in SSMS. It works fine in other tabs (of course) to switch TO the SQL Test tab, but not whilst IN the SQL Test tab to switch to other tabs

      1 vote
      Vote
      Sign in
      Check!
      (thinking…)
      Reset
      or sign in with
      • facebook
      • google
        Password icon
        I agree to the terms of service
        Signed in as (Sign out)
        You have left! (?) (thinking…)
      • Add ability to edit template text.

        Basically, my OCD nature of coding means that the template text doesn't match exactly with what I'd like to see in the comments of my tests. In my case, I want tabs before the comments (not 2 spaces) and don't care about the rest of the header text (the instructions for each section). I'd like to ability to massage the template text into exactly what I want. I'm guessing this is currently generated in CLR code. Is there a way to store this information in the database somewhere so that it's editable?

        4 votes
        Vote
        Sign in
        Check!
        (thinking…)
        Reset
        or sign in with
        • facebook
        • google
          Password icon
          I agree to the terms of service
          Signed in as (Sign out)
          You have left! (?) (thinking…)
        • Request optional 'SQL Test Messages' autofocus

          When having 'SQL Test' and 'SQL Test Messages' in same tab group the 'SQL Test Message' tab pops up and views on top of 'SQL Test' tab.

          I would like to view the SQL Test tree until tests are complete and then review the SQL Test Message spam when I feel the need to.

          1 vote
          Vote
          Sign in
          Check!
          (thinking…)
          Reset
          or sign in with
          • facebook
          • google
            Password icon
            I agree to the terms of service
            Signed in as (Sign out)
            You have left! (?) (thinking…)
          • Tests aren't disconnected on server disconnect

            When I connect to a SQL Server that has tests in a database, it gets added to the SQL Test window. Fine. However, if I then disconnect Object Explorer from that SQL Server, the DBs with tests remain until I quit SSMS.

            1 vote
            Vote
            Sign in
            Check!
            (thinking…)
            Reset
            or sign in with
            • facebook
            • google
              Password icon
              I agree to the terms of service
              Signed in as (Sign out)
              You have left! (?) (thinking…)
            • Need a way to easily disable tests

              There are tests (especially SQLCop tests) that I'd like to be able to disable easily so they don't automatically get run when you execute tSQLt.RunAll.

              I don't want to delete the test, because there's value in knowing the test exists. You may not be able to fix *all* SQLCop issues right away, but you don't want the build to fail until you have time to fix it. There may be a user story to fix the issue, and you'd like to disable the test until the issue is fixed.

              You should be able to disable the test by either
              *…

              1 vote
              Vote
              Sign in
              Check!
              (thinking…)
              Reset
              or sign in with
              • facebook
              • google
                Password icon
                I agree to the terms of service
                Signed in as (Sign out)
                You have left! (?) (thinking…)
              • Add the capability to create a "logical folder" on test hierarchy

                It could be useful to add a capab "create folder" (like "solution folder" in Visual Studio) which allows us to logically separate tests. This can be used for the "Run Test" command.

                11 votes
                Vote
                Sign in
                Check!
                (thinking…)
                Reset
                or sign in with
                • facebook
                • google
                  Password icon
                  I agree to the terms of service
                  Signed in as (Sign out)
                  You have left! (?) (thinking…)
                  1 comment  ·  Admin →
                • expand/collapse all button on view

                  It could be useful to expand and collapse the set of nodes using the dedicate button (like solution explorer on Visual Studio)

                  10 votes
                  Vote
                  Sign in
                  Check!
                  (thinking…)
                  Reset
                  or sign in with
                  • facebook
                  • google
                    Password icon
                    I agree to the terms of service
                    Signed in as (Sign out)
                    You have left! (?) (thinking…)
                  • SET NOCOUNT ON

                    The "Procedures with SET NOCOUNT ON" SQL Cop test is incompatible with the default template for a new test in SQL Test. If you create a test using the "New Test" feature, that will make the "Procedures with SET NOCOUNT ON" start to fail. You can fix this by manually adding "SET NOCOUNT ON" to the test. But if we believe that this is really important enough to have backed by a test, then SQL Test should include SET NOCOUNT ON in its new test template.

                    1 vote
                    Vote
                    Sign in
                    Check!
                    (thinking…)
                    Reset
                    or sign in with
                    • facebook
                    • google
                      Password icon
                      I agree to the terms of service
                      Signed in as (Sign out)
                      You have left! (?) (thinking…)
                    • Inappropriate SQLCop Tests

                      There are some issues with the SQLCop tests that are included with SQL Test 1.5.
                      -"Agent Service": This should be removed from SQL Test as it is not relevant to know if the agent service is running for unit testing a database.
                      -"Compatability Level": This test inappropriately tests all databases on the server rather than just the database under test.
                      -"Instant File Initialization": This test depends on xp_cmdshell. Strangely, it returns a failure if xp_cmdshell isn't enabled. Yet - there is another test that will fail if xp_cmdshell is enabled. This is a bit odd. Since this is more of…

                      2 votes
                      Vote
                      Sign in
                      Check!
                      (thinking…)
                      Reset
                      or sign in with
                      • facebook
                      • google
                        Password icon
                        I agree to the terms of service
                        Signed in as (Sign out)
                        You have left! (?) (thinking…)
                      • Need SetupFixture

                        In many unit test frameworks like NUnit, it is not only possible to have a routine that runs before each test, it is possible to have a one-time routine that is run before any of the tests in a given test class. In NUnit, this attribute is called SetupFixture. When one has a series of tests that they want to run against the same setup, it would be far more efficient to do this work once than to do before each test.

                        1 vote
                        Vote
                        Sign in
                        Check!
                        (thinking…)
                        Reset
                        or sign in with
                        • facebook
                        • google
                          Password icon
                          I agree to the terms of service
                          Signed in as (Sign out)
                          You have left! (?) (thinking…)
                        • Add Team Coherence to your Source Control Providors

                          I am at a company using a legacy DB(FireBird) with Team Coherence as their source control system. We are migrating features from Firebird to a modern, supported db such as SQL2012. But there are many other legacy system that we need to connect into.

                          3 votes
                          Vote
                          Sign in
                          Check!
                          (thinking…)
                          Reset
                          or sign in with
                          • facebook
                          • google
                            Password icon
                            I agree to the terms of service
                            Signed in as (Sign out)
                            You have left! (?) (thinking…)
                          • Auto hide tab

                            The SQL Test tab will not auto hide when the set to auto hide. Please fix this

                            5 votes
                            Vote
                            Sign in
                            Check!
                            (thinking…)
                            Reset
                            or sign in with
                            • facebook
                            • google
                              Password icon
                              I agree to the terms of service
                              Signed in as (Sign out)
                              You have left! (?) (thinking…)
                              1 comment  ·  Admin →
                            • Statistics information query

                              When we perform heavy DMLs on tables updating statistics information becomes necessary for improved query performance. Below is the query which return no. of updated rows along with percent change in statistics so that the user can take decision on updating it.

                              -- To check number of rows updated and percentage of change in order to clean statistics.
                              select schemas.name as table_schema,
                              tbls.name as table_name,
                              i.name as index_name,
                              i.id as table_id,
                              i.indid as index_id,
                              i.rowmodctr as modifiedRows,
                              (select max(rowcnt) from sysindexes i2 where i.id = i2.id and i2.indid < 2) as rowcnt,
                              convert(DECIMAL(18,8), convert(DECIMAL(18,8),i.rowmodctr) / convert(DECIMAL(18,8),(select max(rowcnt) from sysindexes i2…

                              2 votes
                              Vote
                              Sign in
                              Check!
                              (thinking…)
                              Reset
                              or sign in with
                              • facebook
                              • google
                                Password icon
                                I agree to the terms of service
                                Signed in as (Sign out)
                                You have left! (?) (thinking…)
                              • Creating dummy Identity

                                create view MyView as
                                (
                                select row_number() over ( order by col1) as ID, col1 from (
                                Select col1 From Table1
                                Union All
                                Select col1 From Table2
                                ) a
                                )

                                1 vote
                                Vote
                                Sign in
                                Check!
                                (thinking…)
                                Reset
                                or sign in with
                                • facebook
                                • google
                                  Password icon
                                  I agree to the terms of service
                                  Signed in as (Sign out)
                                  You have left! (?) (thinking…)
                                • Parsing delimited (,) string

                                  declare
                                  v_sql VARCHAR2(2000);
                                  v_pos INTEGER;
                                  v_differentiator VARCHAR2(10);
                                  v_sql_temp VARCHAR2(1000);
                                  v_pos_temp INTEGER;
                                  begin
                                  v_pos := 1;
                                  v_sql := 'a,abc,abcde,ab,sos,sooooo,oooops,posh,';
                                  v_differentiator := ',';
                                  v_sql_temp := v_sql;
                                  while (instr (v_sql,v_differentiator,v_pos) > 0 )
                                  loop
                                  v_pos_temp := instr(v_sql,v_differentiator,v_pos );
                                  v_sql_temp := substr(v_sql,v_pos,(v_pos_temp-v_pos));
                                  v_pos := v_pos_temp +1;
                                  --dbms_output.put_line (v_sql_temp);
                                  insert into temp_comma(col1) values (v_sql_temp);
                                  end loop;
                                  end;

                                  This query will help in parsing any delimited string (in above example it is using ',' as parsing delimiter. This query can be useful in many business scenarios where in we have input data as a long string containing delimited values.

                                  1 vote
                                  Vote
                                  Sign in
                                  Check!
                                  (thinking…)
                                  Reset
                                  or sign in with
                                  • facebook
                                  • google
                                    Password icon
                                    I agree to the terms of service
                                    Signed in as (Sign out)
                                    You have left! (?) (thinking…)
                                  • To get list of all artifact in DB

                                    /** SCRIPT TO GET ALL TABLES FROM A SERVER
                                    CREATED BY : SADAF KHAN
                                    **/
                                    DECLARE @SQL VARCHAR(MAX) -- database name
                                    DECLARE @NAME VARCHAR(100) -- path for backup files
                                    DECLARE db_cursor CURSOR FOR
                                    SELECT DISTINCT NAME
                                    FROM MASTER.DBO.SYSDATABASES
                                    WHERE NAME NOT IN ('master','model','msdb','tempdb')
                                    Execute ('DROP TABLE TMP_ALL_TABLE_NAMES');
                                    Execute ('CREATE TABLE TMP_ALL_TABLE_NAMES( DB_NAME VARCHAR(100), TABLE_NAME VARCHAR(100))');

                                    OPEN DB_CURSOR
                                    FETCH NEXT FROM DB_CURSOR INTO @NAME

                                    WHILE @@FETCH_STATUS = 0
                                    BEGIN
                                    SET @SQL = 'INSERT INTO TMP_ALL_TABLE_NAMES SELECT DISTINCT '''+@name+''', NAME FROM '+ @NAME +'.SYS.tables';
                                    Execute(@sql);
                                    FETCH NEXT
                                    FROM DB_CURSOR INTO @NAME
                                    END

                                    CLOSE DB_CURSOR
                                    DEALLOCATE DB_CURSOR

                                    /

                                    This query will give you…

                                    1 vote
                                    Vote
                                    Sign in
                                    Check!
                                    (thinking…)
                                    Reset
                                    or sign in with
                                    • facebook
                                    • google
                                      Password icon
                                      I agree to the terms of service
                                      Signed in as (Sign out)
                                      You have left! (?) (thinking…)
                                    • Get primary key for all table

                                      In order to get primary key details for all tabes below query is useful. Business use case - Many time we need to do a quick analysis for all the primary key constraints present in DB for all tables and doing it manually is a great pain when the DB is big. Below is query to get primary key for all tables within a database.

                                      with ChildFolders
                                      as
                                      (
                                      select PARENT.parentfolderid, PARENT.folderid, PARENT.foldername,
                                      cast('' as sysname) as RootFolder,
                                      cast(PARENT.foldername as varchar(max)) as FullPath,
                                      0 as Lvl
                                      from msdb.dbo.sysssispackagefolders PARENT
                                      where PARENT.parentfolderid is null
                                      UNION ALL
                                      select CHILD.parentfolderid, CHILD.folderid, CHILD.foldername,
                                      case…

                                      1 vote
                                      Vote
                                      Sign in
                                      Check!
                                      (thinking…)
                                      Reset
                                      or sign in with
                                      • facebook
                                      • google
                                        Password icon
                                        I agree to the terms of service
                                        Signed in as (Sign out)
                                        You have left! (?) (thinking…)
                                      • Taking backup of all user database

                                        In order to take automated backup of all user databases below is the query.
                                        This query will eliminate use of manual backups for user databases, in order to fully automate this just create a SQL Agent job and write this query in the job and forget about taking any manual DB backups.

                                        DECLARE @name VARCHAR(50) -- database name
                                        DECLARE @path VARCHAR(256) -- path for backup files
                                        DECLARE @fileName VARCHAR(256) -- filename for backup
                                        DECLARE @fileDate VARCHAR(20) -- used for file name

                                        SET @path = 'C:\DB_BKPUP\'

                                        SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

                                        DECLARE db_cursor CURSOR FOR
                                        SELECT name
                                        FROM master.dbo.sysdatabases
                                        WHERE name NOT…

                                        1 vote
                                        Vote
                                        Sign in
                                        Check!
                                        (thinking…)
                                        Reset
                                        or sign in with
                                        • facebook
                                        • google
                                          Password icon
                                          I agree to the terms of service
                                          Signed in as (Sign out)
                                          You have left! (?) (thinking…)
                                        • have a filter on the database being worked on

                                          We have many customer databases, all of a similar schema, all with tSQLt installed. A typical developer machine may have 8-10 of these databases on.

                                          It is very hard to see which database to work on in the SQLTest as the tree view just expands them all.

                                          1 vote
                                          Vote
                                          Sign in
                                          Check!
                                          (thinking…)
                                          Reset
                                          or sign in with
                                          • facebook
                                          • google
                                            Password icon
                                            I agree to the terms of service
                                            Signed in as (Sign out)
                                            You have left! (?) (thinking…)
                                          ← Previous 1 3 4
                                          • Don't see your idea?

                                          SQL Test forum

                                          Feedback and Knowledge Base