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. 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…)
                  • 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…)
                    • 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…)
                      • 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…)
                        • 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…)
                          • 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…)
                            • 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…)
                              • Show the actual reason for the Test failure in Testing Dynamic SQL Standard Test

                                The "test Procedures using dynamic SQL without sp_executesql" fails and points to a website that waffles on about some stuff which seem to indicate that the problem relates to datatypes. But Your test rig doesn't indicate which var is being incorrectly converted.

                                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…)
                                • new standardtest : Find Nullable Bit Columns without default

                                  MS-access has problems with non-nullable bit columns so you can either use (tiny)int columns or make them nullable but have a default value

                                  ALTER PROC tools.find_nullable_columns_without_default_bit_only
                                  as
                                  BEGIN
                                  -------------------------------------------------------------------------------
                                  -- Finds all NULL-able columns that have no DEFAULT constraint. Also emits the
                                  -- ALTER TABLE statements needed to add the DEFAULT constraints
                                  -------------------------------------------------------------------------------
                                  -- Copyright 2009 Larry Leonard, Definitive Solutions Inc.
                                  -- http://www.DefinitiveSolutions.com
                                  --
                                  -- Copying and distribution of this file, with or without modification, are
                                  -- permitted in any medium without royalty provided the copyright notice and
                                  -- this notice are preserved. This file is offered as-is, without any…

                                  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…)
                                  • tSQLt window popup fail

                                    When tSQLt window pups up all content (nodes, buttons, text) in it blurs and spreads all over the surface of the window.

                                    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…)
                                    • Don't require installing objects in your database for unit testing

                                      I have to remove the objects it creates on my database before they go to production. QA can't run these at all as they are not allowed to create/alter any database objects.

                                      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…)
                                      • Webbased interface

                                        SQL Server Management Studio is required for this tool. An additional Webbased interface with just basic editing options would be great. This interface would only serve the testteam who can create and maintain test-scripts and does not require installing 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…)
                                        • Better log/audit logging

                                          The test results in the table [Test.Result] contains data from the last run. We are looking for a tool which records all logs for each run. So, we could schedule the test to run daily and create a dashboard based on the runs.

                                          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