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]

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/

[Greg D. Moore \(Strider\)]

"db55" <ch****@gmail.c om> wrote in message
news:11******** **************@ v46g2000cwv.goo glegroups.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?

[jsfromynr]

Hi db55,

I think this will help you

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

With Warm regards
Jatinder Singh
http://jatindersingh.blogspot.com

[db55]

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...