Create Status Table

[db55]

I have >200 tables and I want to create a table that lists the name of
each table, the number of records, and the number of locations within
the table.

I've created a cursor to do this but it doesn't like it. I get the
following error.

Invalid column name '<tablename>'.

Here's my script

DECLARE @tbl varchar(100)
DECLARE @sql varchar(1000)
-- Insert statements for procedure here
declare c_table cursor for
select table_name from INFORMATION_SCH EMA.TABLES where table_type =
'base table' order by table_name

open c_table
fetch next from c_table into @tbl

while (@@fetch_status = 0)
begin

set @SQL = 'INSERT INTO [zzTable_Status]
SELECT ('+ @tbl +') as tblname, count(distinct station__no),
count(station__ no)
FROM [bronze_views].'+@tbl+''

exec (@SQL)

Print @tbl + ' Updated'

fetch next from c_table into @tbl
end
close c_table
deallocate c_table

Any help is appreciated...

[SQL]

try changing this
select table_name from INFORMATION_SCH EMA.TABLES where table_type =
'base table' order by table_name

to this

select quotename(table _name)
from INFORMATION_SCH EMA.TABLES where table_type =
'base table' order by table_name

you probably have a table name with a space in the name

Denis the SQL Menace
http://sqlservercode.blogspot.com/

[db55]

Unfortunately, that didn't work. I still got the same error the table
just appear with brackets.

Any other ideas?

[SQL]

change exec to print and look at the code generated
If I change exec to print the cript runs without a problem
Do all table have this same column name station__no ?

Denis the SQL Menace
http://sqlservercode.blogspot.com/

[db55]

To answer your question - Yes every table has that column. I think
what the problem is with the script is that I need to insert the name
of the table into the zzTable_Status table. This needs to be marked as
a string. So, '<tablename>' needs to be in the insert script.

Basically, I want the name of the table, number of station__no's in the
same table, and the number or records in the same table.

or another example

select 'cityname', count(distinct streets), count(streets) from
cityname.

What am I missing?

I hate it when you know what you want but you can't think of it.

I appreciate your help!

I have your blog as one of my rss feeds. I'll try to help your adsense
account. :-)

[SQL]

I see, you need triple quotes to store the table name
This should do it

set @SQL = 'INSERT INTO [zzTable_Status]
SELECT '''+ @tbl +''' , count(distinct station__no),
count(station__ no)
FROM [bronze_views].'+@tbl+''
Denis the SQL Menace
http://sqlservercode.blogspot.com/

[db55]

No dice! <darn it!>

I still get invalid object name '<table_name>' .

Let's try skinning this cat a different way. Do you have any
suggestions.

I want to create a table (or view) with the name of each of the tables
in the db, the number of times a location appears, and the total number
of records. I'm sure someone has done this before.

Any suggestions?

[SQL]

I have no problems running this in the pubs DB

use pubs

go
create table zzTable_Status (tblname varchar(600),Di stinctCount
int,RegularCoun t int)
Go

DECLARE @tbl varchar(100)
DECLARE @sql varchar(1000)
-- Insert statements for procedure here
declare c_table cursor for
select table_name from INFORMATION_SCH EMA.TABLES where table_type =
'base table' order by table_name
open c_table
fetch next from c_table into @tbl
while (@@fetch_status = 0)
begin
set @SQL = 'INSERT INTO [zzTable_Status]
SELECT ('''+ @tbl +''') as tblname, count(*),
count(*)
FROM .'+@tbl+''
exec (@SQL)
Print @tbl + ' Updated'
fetch next from c_table into @tbl
end
close c_table
deallocate c_table
select * from zzTable_Status
drop table zzTable_Status

Denis the SQL Menace
http://sqlservercode.blogspot.com/

[db55]

I still get invalid object name errors.

I'm trying to do this in 2005. Maybe there is something I haven't set
right or something.

This is frustrating...

[SQL]

I know what the problem is it's the schema
This will run I just tried it on 2005
this is the change
select table_schema +'.' + table_name from INFORMATION_SCH EMA.TABLES
where table_type =
'base table' order by table_name
use adventureworks
go
create table zzTable_Status (tblname varchar(600),Di stinctCount
int,RegularCoun t int)
Go
DECLARE @tbl varchar(100)
DECLARE @sql varchar(1000)
-- Insert statements for procedure here
declare c_table cursor for
select table_schema +'.' + table_name from INFORMATION_SCH EMA.TABLES
where table_type =
'base table' order by table_name
open c_table
fetch next from c_table into @tbl
while (@@fetch_status = 0)
begin
set @SQL = 'INSERT INTO [zzTable_Status]
SELECT ('''+ @tbl +''') as tblname, count(*),
count(*)
FROM .'+@tbl+''
exec (@SQL)
Print @tbl + ' Updated'
fetch next from c_table into @tbl
end
close c_table
deallocate c_table
select * from zzTable_Status
drop table zzTable_Status

Denis the SQL Menace
http://sqlservercode.blogspot.com/