Create Status Table

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_SCHEMA.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...try changing this
select table_name from INFORMATION_SCHEMA.TABLES where table_type =
'base table' order by table_name

to this

select quotename(table_name)
from INFORMATION_SCHEMA.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/|||Unfortunately, that didn't work. I still got the same error the table
just appear with brackets.

Any other ideas?|||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/|||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. :-)|||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/|||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?|||I have no problems running this in the pubs DB

use pubs

go
create table zzTable_Status (tblname varchar(600),DistinctCount
int,RegularCount 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_SCHEMA.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/|||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...|||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_SCHEMA.TABLES
where table_type =
'base table' order by table_name

use adventureworks

go
create table zzTable_Status (tblname varchar(600),DistinctCount
int,RegularCount 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_SCHEMA.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/|||also take out the dot here
The code works but the dot shouldn't be there anyway
instead of this
FROM .'+@.tbl+''
use this
FROM '+@.tbl+''

Denis the SQL Menace
http://sqlservercode.blogspot.com/|||"db55" <chfran@.gmail.com> wrote in message
news:1147798134.372688.254340@.v46g2000cwv.googlegr oups.com...
> 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.

Out of curiousity, WHY?|||Hi db55,

I think this will help you

Create Table TableNameRow
(
TableName varchar(100) Not Null,
recs int
)
Go
Exec sp_msforeachtable 'Insert into TableNameRow Select ''?'',count(1)
from ?'
Go
Select * from TableNameRow

With Warm regards
Jatinder Singh
http://jatindersingh.blogspot.com|||I want to track the status of each table. I'm importing millions of
records into these tables and I want to track them. I also create
reports for my mgmt with the data pulls.

I always make sure I ask that question so I'm not wasting my time.

Thanks for asking...