4 Replies Latest reply: Feb 15, 2017 9:25 AM by Jason Campbell RSS

    SQL Cursor Question

    Jason Campbell

      I'm trying to incorporate an existing SQL script into QV and not having much success.  SQL isn't my strongest area...

       

      We need a Network/DB schema that includes Hash ID's.  I'm not sure how to implement it.

       

      Here's what I have so far:

       

      Connection string;

       

      //Build base table

      Execute('SELECT  s.name AS SchemaName

      ,       t.name AS TableName

      ,       c.name AS ColumnName

      ,       0 AS IsHashID

      INTO #SchemaCrawler

      FROM    sys.tables t

              INNER JOIN sys.columns c ON c.object_id = t.object_id

              INNER JOIN sys.schemas s ON s.schema_id = t.schema_id');

       

      QV says there is a script line error.  Am I going about this the right way?

       

      Thanks!

      JC

        • Re: SQL Cursor Question
          popescu cosmina

          Hi,

           

          Try:

          Connection string;


          SchemaCrawler:

          Load *;

          SQL SELECT  s.name AS SchemaName

          ,       t.name AS TableName

          ,       c.name AS ColumnName

          ,       0 AS IsHashID

          FROM    sys.tables t

                  INNER JOIN sys.columns c ON c.object_id = t.object_id

                  INNER JOIN sys.schemas s ON s.schema_id = t.schema_id;

          • Re: SQL Cursor Question
            Jason Campbell

            Thank you both for your assistance.  I now have all of the code that I need to incorporate into QV.  

             

            I have to loop it through nine (9) servers.  Any thoughts as to how it can be implemented?

             

            //Build base table

            SELECT  s.name AS SchemaName

            ,       t.name AS TableName

            ,       c.name AS ColumnName

            ,             0 AS IsHashID

            INTO #SchemaCrawler

            FROM    sys.tables t

                    INNER JOIN sys.columns c ON c.object_id = t.object_id

                    INNER JOIN sys.schemas s ON s.schema_id = t.schema_id;

             

             

            //Run a cursor

            DECLARE @database

            DECLARE @schema VARCHAR(15)

            DECLARE @table VARCHAR(255)

            DECLARE @column VARCHAR(255)

            DECLARE @sql VARCHAR(MAX)

             

             

            DECLARE LoopTables CURSOR FAST_FORWARD READ_ONLY FOR 

            SELECT s.name, t.name, c.name

            FROM sys.tables t

            INNER JOIN sys.columns c ON c.object_id = t.object_id

            INNER JOIN sys.schemas s ON s.schema_id = t.schema_id

            WHERE c.name = 'hashid'

            AND t.name = 'plsubdatahash'

             

             

            OPEN LoopTables

             

             

            FETCH NEXT FROM LoopTables INTO @schema, @table, @column

             

             

            WHILE @@FETCH_STATUS = 0

            BEGIN

               

                   SELECT @sql = 'INSERT INTO #SchemaCrawler (SchemaName, TableName, ColumnName, IsHashID) SELECT DISTINCT ''' + @schema + ''' as SchemaName, ''' + @table + ''' as TableName, hashid AS ColumnName, 1 as IsHashID FROM ' + @schema + '.' + @table

                   EXEC sp_executesql @sql

                FETCH NEXT FROM LoopTables INTO @schema, @table, @column

            END

             

             

            CLOSE LoopTables

            DEALLOCATE LoopTables

             

             

            SELECT  *

            FROM  #SchemaCrawler 

            • Re: SQL Cursor Question
              Jason Campbell

              Please disregard this post. 

               

              After getting clarification on the requirements, I need to start a new post.