problem in getting table names

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

problem in getting table names

Author

Topic

smithersgs
Starting Member

17 Posts

Posted - 2008-09-23 : 13:55:34

Hi, I have a database of which tables have the same structure (same field names) for some reason. What I wanted was to update a field in those tables. I wrote a script as follows, but got some strange result from print command and error.

--------------------
declare @start int, @end int, @tablename varchar
select name, identity(int,1,1) as rowid into #temp from sysobjects where xtype='U' order by name

select @start=1, @end=@@rowcount
while (@start<=@end)
begin
select @tablename=name, @start=@start+1 from #temp where rowid=@start
print @tablename
update @tablename set [to]='test' where [to]='CE'
end
drop table #temp
------------------

1. print command prints only the first letter of the table name.
2. error at Update command - must declare the table variable "@tablename". It is already declared.

What am I missing?
Thanks,

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-09-23 : 13:59:21

You can use sp_MSforeachtable instead to update tables.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-23 : 14:01:52

quote:
Originally posted by smithersgs

Hi, I have a database of which tables have the same structure (same field names) for some reason. What I wanted was to update a field in those tables. I wrote a script as follows, but got some strange result from print command and error.

--------------------
declare @start int, @end int, @tablename varchar(length)
select name, identity(int,1,1) as rowid into #temp from sysobjects where xtype='U' order by name

select @start=1, @end=@@rowcount
while (@start<=@end)
begin
select @tablename=name, @start=@start+1 from #temp where rowid=@start
print @tablename
update @tablename set [to]='test' where [to]='CE'
end
drop table #temp
------------------

1. print command prints only the first letter of the table name.
2. error at Update command - must declare the table variable "@tablename". It is already declared.

What am I missing?
Thanks,

1. this is because you'vent specified a length for varchar field. so it will take default length which is 1.
2.You cant directly use variable for object names in update. you need dynamic sql for it.

smithersgs
Starting Member

17 Posts

Posted - 2008-09-23 : 14:09:56

Does SQL Express has sp_MSforeachtable?

smithersgs
Starting Member

17 Posts

Posted - 2008-09-23 : 14:12:55

visakh16,
Thanks for your reply.
Could you give me an example of dynamic sql?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-23 : 14:14:33

quote:
Originally posted by smithersgs

visakh16,
Thanks for your reply.
Could you give me an example of dynamic sql?

for your case it would be

DECLARE @Sql varchar(8000)

SET @Sql='update '+ @tablename + ' set [to]=''test'' where [to]=''CE'''

EXEC(@Sql)

this should be inside loop.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-09-23 : 14:17:06

quote:
Originally posted by smithersgs

Does SQL Express has sp_MSforeachtable?

Yes.

smithersgs
Starting Member

17 Posts

Posted - 2008-09-23 : 15:15:19

visakh16 - Thanks for your help. It worked.

sodeep - i looked it up, but dont know how to use it for my case. Can you help with more details? Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-24 : 01:44:21

quote:
Originally posted by smithersgs

visakh16 - Thanks for your help. It worked.

sodeep - i looked it up, but dont know how to use it for my case. Can you help with more details? Thanks.

EXEC sp_Msforeachtable 'update ? set [to]=''test'' where [to]=''CE'''

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources