Store procedure possible query

• Ged Aitchison

  Valued Member

  Points: 54

  More actions

  November 2, 2006 at 10:53 pm

  #91888

  Hi all,

  Effectively, what I am trying to achieve is to query a number of tables to see where there may be duplicates.

  What I thought of doing is get the base query done and using a stored proc so that a parameter of a tablename can be passed into it. 

  So effectively I want join 2 queries together and the results of 1 will be the table name in the from clause of the other

   

  e.g.

  query 1

  select table_name from information_schema.tables where table_name like 'stWA1%' or table_name like 'staNA1%';

  The results will look something like

  stNA1Table1

  stNA1Table2

  stWA1Table1

  stWA1Table2

   

  These results need to be fetch and added into 2nd queries from clause.  The 2nd query will look like

   

  select fielda, fieldb

  from @ResultQuery1

  group by fielda, fieldb

  having count(fieldb) >1

   

  So.  What I thought may be a solution is the following.  Please let me know if I am on the right track and what it is I am not doing correctly.

  ====================================================================

  DECLARE abc CURSOR FOR SELECT table_name FROM information_schema.tables

  where table_name like 'stNA1%' or table_name like 'stWA1%'

  OPEN abc

  FETCH NEXT FROM abc

    WHILE (@@FETCH_STATUS = 0)

  @GroupID   varchar(50)

  select legacyTableName, legacyValue FROM @GroupID

  group by legacyTableName, legacyValue

  having count(legacyValue) > 1

      

       -- Perform all actions

     FETCH NEXT FROM abc

  CLOSE abc

  DEALLOCATE abc

  GO

  ====================================================================

  Thanks in advance.

   

   

• Jeff Moden

  SSC Guru

  Points: 1004441

  More actions

  November 3, 2006 at 7:46 am

  #669785

  For query 1... it seems a view of the tables would be appropriate (do be warned that there is a limit to the number of unions you can do... I think it's 255 but not sure) ...

  --===== Declare some variables to hold dynamic SQL in

  DECLARE @SQLHead VARCHAR(8000)

  DECLARE @SQLBody VARCHAR(8000)

  --===== Create the CREATE portion of the dynamic SQL

      SET @SQLHead = 'CREATE VIEW vAllMyTables AS'+CHAR(13)

  --===== Create the body of the view in dynamic SQL

   SELECT @SQLBody = ISNULL(@SQLBody+' UNION ALL','')+CHAR(13)

                   + 'SELECT ColA,ColB,'''+Table_Name+''' AS TableName FROM ' + Table_Name

     FROM Information_Schema.Tables

    WHERE Table_Type = 'Base Table'

       AND (   Table_Name LIKE 'stWA1%'

            OR Table_Name LIKE 'staNA1%')

  --===== If the view already exists, drop it so can recreate

       IF OBJECT_ID('vAllMyTables') IS NOT NULL

          DROP VIEW vAllMyTables

  --===== Print the dynamic SQL just for grins

    PRINT @SQLHead+@SQLBody

  --===== Create the view

     EXEC (@SQLHead+@SQLBody)

  ... then, for the second query...

   SELECT fielda, fieldb

     FROM vAllMyTables

    GROUP BY fielda, fieldb

    HAVING COUNT(*) >1

   

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)