By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,856 Members | 2,149 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,856 IT Pros & Developers. It's quick & easy.

Create Status Table

P: n/a
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...

May 16 '06 #1
Share this Question
Share on Google+
13 Replies


P: n/a
SQL
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/

May 16 '06 #2

P: n/a
Unfortunately, that didn't work. I still got the same error the table
just appear with brackets.

Any other ideas?

May 16 '06 #3

P: n/a
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/

May 16 '06 #4

P: n/a
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. :-)

May 16 '06 #5

P: n/a
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/

May 16 '06 #6

P: n/a
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?

May 16 '06 #7

P: n/a
SQL
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/

May 16 '06 #8

P: n/a
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...

May 16 '06 #9

P: n/a
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_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/

May 16 '06 #10

P: n/a
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/

May 16 '06 #11

P: n/a

"db55" <ch****@gmail.com> wrote in message
news:11**********************@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?

May 17 '06 #12

P: n/a
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

May 17 '06 #13

P: n/a
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...

May 24 '06 #14

This discussion thread is closed

Replies have been disabled for this discussion.