Professional Web Applications Themes

Using variable in From clause - Microsoft SQL / MS SQL Server

I want to write a T-Sql script that will cycle through all of the tables in a database and write the number of records in each table. I have the cycling and writing fine, but when I try to execute sql with a variable as the table name is doesn't work. Can anyone tell me how to accomplish this? The sql I am trying to execute is something like: Select MyCount = Count(*) from TableName...

  1. #1

    Default Using variable in From clause

    I want to write a T-Sql script that will cycle through all
    of the tables in a database and write the number of
    records in each table. I have the cycling and writing
    fine, but when I try to execute sql with a variable as the
    table name is doesn't work. Can anyone tell me how to
    accomplish this?

    The sql I am trying to execute is something like:

    Select MyCount = Count(*) from TableName

    Glenn Stein Guest

  2. #2

    Default Re: Using variable in From clause

    Glenn,

    [url]http://www.algonet.se/~sommar/dynamic_sql.html#Dyn_table[/url]

    --
    Dinesh.
    SQL Server FAQ at
    [url]http://www.tkdinesh.com[/url]

    "Glenn Stein" <glenn.steinciticorp.com> wrote in message
    news:039001c3419d$ea274960$a301280aphx.gbl...
    > I want to write a T-Sql script that will cycle through all
    > of the tables in a database and write the number of
    > records in each table. I have the cycling and writing
    > fine, but when I try to execute sql with a variable as the
    > table name is doesn't work. Can anyone tell me how to
    > accomplish this?
    >
    > The sql I am trying to execute is something like:
    >
    > Select MyCount = Count(*) from TableName
    >

    Dinesh.T.K Guest

  3. #3

    Default Re: Using variable in From clause

    [url]www.aspfaq.com/2428[/url]



    "Glenn Stein" <glenn.steinciticorp.com> wrote in message
    news:039001c3419d$ea274960$a301280aphx.gbl...
    > I want to write a T-Sql script that will cycle through all
    > of the tables in a database and write the number of
    > records in each table. I have the cycling and writing
    > fine, but when I try to execute sql with a variable as the
    > table name is doesn't work. Can anyone tell me how to
    > accomplish this?
    >
    > The sql I am trying to execute is something like:
    >
    > Select MyCount = Count(*) from TableName
    >

    Aaron Bertrand - MVP Guest

  4. #4

    Default Re: Using variable in From clause

    Thank you!!!
    >-----Original Message-----
    >Glenn,
    >
    >[url]http://www.algonet.se/~sommar/dynamic_sql.html#Dyn_table[/url]
    >
    >--
    >Dinesh.
    >SQL Server FAQ at
    >[url]http://www.tkdinesh.com[/url]
    >
    >"Glenn Stein" <glenn.steinciticorp.com> wrote in message
    >news:039001c3419d$ea274960$a301280aphx.gbl...
    >> I want to write a T-Sql script that will cycle through
    all
    >> of the tables in a database and write the number of
    >> records in each table. I have the cycling and writing
    >> fine, but when I try to execute sql with a variable as
    the
    >> table name is doesn't work. Can anyone tell me how to
    >> accomplish this?
    >>
    >> The sql I am trying to execute is something like:
    >>
    >> Select MyCount = Count(*) from TableName
    >>
    >
    >
    >.
    >
    Glenn Stein Guest

  5. #5

    Default Re: Using variable in From clause

    Jacco
    Went to BOL and found what you are talking about....
    Thanks for the tip.
    bob

    "Jacco Schalkwijk" <NOSPAMjaccoseurostop.co.uk> wrote in message
    news:efvBAMaQDHA.1564TK2MSFTNGP12.phx.gbl...
    > That query doesn't return all the tables, and rows in sysindexes is not up
    > to date unless you run DBCC UPDATEUSAGE first, use this instead:
    >
    > DBCC UPDATEUSAGE (0)
    >
    > select sysobjects.name [name of table], rows [number of rows]
    > from sysobjects
    > inner join sysindexes
    > on sysobjects.id = sysindexes.id
    >
    > where xtype='u' and sysindexes.indid in (0,1)
    > order by sysobjects.[number of rows] desc
    >
    >
    >
    >
    > "BobMcClellan" <bobmcctricoequipment.com> wrote in message
    > news:%23V$PGCaQDHA.2176TK2MSFTNGP12.phx.gbl...
    > > this was in another post.....
    > > Table Names & RecordCounts per Table
    > >
    > > select sysobjects.name [name of table], rows [number of rows]
    > > from sysobjects
    > > inner join sysindexes on sysobjects.id = sysindexes.id and
    > sysobjects.name
    > > = sysindexes.name
    > > where xtype='u'
    > > order by sysobjects.[number of rows] desc
    > >
    > > hth,
    > > bob
    > >
    > > "Dinesh.T.K" <tkdineshnospam.mail.tkdinesh.com> wrote in message
    > > news:ubAAI6ZQDHA.2424tk2msftngp13.phx.gbl...
    > > > Glenn,
    > > >
    > > > [url]http://www.algonet.se/~sommar/dynamic_sql.html#Dyn_table[/url]
    > > >
    > > > --
    > > > Dinesh.
    > > > SQL Server FAQ at
    > > > [url]http://www.tkdinesh.com[/url]
    > > >
    > > > "Glenn Stein" <glenn.steinciticorp.com> wrote in message
    > > > news:039001c3419d$ea274960$a301280aphx.gbl...
    > > > > I want to write a T-Sql script that will cycle through all
    > > > > of the tables in a database and write the number of
    > > > > records in each table. I have the cycling and writing
    > > > > fine, but when I try to execute sql with a variable as the
    > > > > table name is doesn't work. Can anyone tell me how to
    > > > > accomplish this?
    > > > >
    > > > > The sql I am trying to execute is something like:
    > > > >
    > > > > Select MyCount = Count(*) from TableName
    > > > >
    > > >
    > > >
    > >
    > >
    >
    >

    BobMcClellan Guest

Similar Threads

  1. where clause as variable string
    By RichardG in forum Coldfusion Database Access
    Replies: 2
    Last Post: April 28th, 09:54 PM
  2. Using IN in a WHERE clause
    By drmaves in forum Coldfusion Database Access
    Replies: 15
    Last Post: September 5th, 05:05 PM
  3. CF MX 6 WHERE Clause
    By Jeremy5431 in forum Macromedia ColdFusion
    Replies: 13
    Last Post: May 2nd, 02:26 PM
  4. Using COUNT() In WHERE Clause
    By MDW in forum ASP Database
    Replies: 1
    Last Post: February 24th, 02:51 PM
  5. connect by clause
    By shankar72 in forum Oracle Server
    Replies: 0
    Last Post: July 10th, 08:56 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139