Using the With Clause SQL Server 2008

Question

Can someone show me a sample SQL server script that I can look at that uses the  With Clause      I am trying to use this clause to iterate through 200 databases that contain the same table that I am trying to run a query on   I am trying to avoid using a cursor because the query time takes too long as well as using a while a loop     Can someone advise me as to what I can do     Thank you

User · Answer

There are two types of WITH clauses   Here is the FizzBuzz in SQL form  using a WITH common table expression  CTE     WITH mil AS    SELECT TOP 1000000 ROW NUMBER   OVER   ORDER BY c column id    n   FROM master sys all columns as c  CROSS JOIN master sys all columns as c2                    SELECT CASE WHEN n    3   0 THEN              CASE WHEN n    5   0 THEN  FizzBuzz  ELSE  Fizz  END         WHEN n   5   0 THEN  Buzz          ELSE CAST n AS char 6        END   CHAR 13   FROM mil   Here is a select statement also using a WITH clause  SELECT   FROM orders WITH  NOLOCK  where order id   123

User · Answer

Just a poke  but here s another way to write FizzBuzz    100 rows is enough to show the WITH statement  I reckon    WITH t100 AS    SELECT n number  FROM master  spt values  WHERE type  P  and number between 1 and 100                    SELECT     ISNULL NULLIF      CASE WHEN n   3   0 THEN  Fizz  Else    END       CASE WHEN n   5   0 THEN  Buzz  Else    END       RIGHT n 3    FROM t100   But the real power behind WITH  known as Common Table Expression http   msdn microsoft com en-us library ms190766 aspx  CTE   in SQL Server 2005 and above is the Recursion  as below where the table is built up through iterations adding to the virtual-table each time    WITH t100 AS    SELECT n 1  union all  SELECT n 1  FROM t100  WHERE n  lt  100                    SELECT     ISNULL NULLIF      CASE WHEN n   3   0 THEN  Fizz  Else    END       CASE WHEN n   5   0 THEN  Buzz  Else    END       RIGHT n 3    FROM t100   To run a similar query in all database  you can use the undocumented sp msforeachdb  It has been mentioned in another answer  but it is sp msforeachdb  not sp foreachdb   Be careful when using it though  as some things are not what you expect  Consider this example  exec sp msforeachdb  select count    from sys objects    Instead of the counts of objects within each DB  you will get the SAME count reported  begin that of the current DB  To get around this  always  use  the database first  Note the square brackets to qualify multi-word database names   exec sp msforeachdb  use      select count    from sys objects    For your specific query about populating a tally table  you can use something like the below  Not sure about the DATE column  so this tally table has only the DBNAME and IMG COUNT columns  but hope it helps you   create table  tbl  dbname sysname  img count int    exec sp msforeachdb   use      if object id   tbldoc    is not null insert  tbl select        count    from tbldoc   select   from  tbl

User · Answer

Try the sp foreachdb procedure

[sql-server-2008] Using the "With Clause" SQL Server 2008

Examples related to sql-server-2008

Examples related to sql-server-2008-r2