get table names. - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

get table names.

Author

Topic

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2007-05-21 : 11:41:32

I like to print the table names that starts with 'table'.
I'm getting the same table name four times. Instead of four different table name.

create table table1(tableID int, tableName varchar (30))
insert table1
select 1, 'tableSam' union all
select 2, 'tableDaniel' union all
select 3, 'tableRon' union all
select 4, 'tableKen' union all
select 5, 'tableHonda'

create table table2(tableID int, tableName varchar (30))
insert table2
select 6, 'tableSam' union all
select 7, 'tableDaniel' union all
select 8, 'tableRon' union all
select 9, 'tableKen' union all
select 10, 'tableHonda'

create table table3(tableID int, tableName varchar (30))
insert table3
select 11, 'tableSam' union all
select 12, 'tableDaniel' union all
select 13, 'tableRon' union all
select 14, 'tableKen' union all
select 15, 'tableHonda'

create table table4 (tableID int, tableName varchar (30))
insert table4
select 16, 'tableSam' union all
select 17, 'tableDaniel' union all
select 18, 'tableRon' union all
select 19, 'tableKen' union all
select 10, 'tableHonda'

declare @string varchar(330)
declare @tableName varchar(30)
declare @count int
set @count = 0

while (@count < 5)
begin

select @tableName = table_name from information_schema.tables
where table_name like 'table%'

exec(@string)
print @tablename

set @count = @count + 1
end

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-21 : 11:47:10

Remove while loop and simply run

select table_name from information_schema.tables
where table_name like 'table%'

Madhivanan

Failing to plan is Planning to fail

nr
SQLTeam MVY

12543 Posts

Posted - 2007-05-21 : 11:48:32

select @tableName = table_name from information_schema.tables
where table_name like 'table%'

That will always get the same tablename

select @tableName = table_name from information_schema.tables
where table_name like 'table%' and table_name > coalesce(@tableName, '')

and get rid of
exec(@string)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kristen
Test

22859 Posts

Posted - 2007-05-21 : 11:49:07

"I'm getting the same table name four times"

That's coz you are doing:


select @tableName = table_name from information_schema.tables
where table_name like 'table%'

four times

It isn't guaranteed to give the same result, because you haven't got an ORDER BY, but it is likely to.

You either need to use a CURSOR or some other form of looping the resultset.

Kristen

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2007-05-21 : 12:01:24

madhivanan,
I know you can use that statement to get the 4 tables names. But that is not my point.

nr,
that doesn't work. It printed the same table 5 times.

kristen,
Cursor is slow and i'm trying to avoid it. That's why i wanna come out with some sort of loop. But my loop is not working.

Kristen
Test

22859 Posts

Posted - 2007-05-21 : 12:06:29

"nr,
that doesn't work"

Add:

ORDER BY table_name

Kristen

nr
SQLTeam MVY

12543 Posts

Posted - 2007-05-21 : 12:17:09

s.b.

select @tableName = min(table_name) from information_schema.tables
where table_name like 'table%' and table_name > coalesce(@tableName, '')

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kristen
Test

22859 Posts

Posted - 2007-05-21 : 14:37:17

Ha! Much better ...

"s.b."

??

Kristen

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-21 : 15:49:18

quote:
Originally posted by funketekun

madhivanan,
I know you can use that statement to get the 4 tables names. But that is not my point.

nr,
that doesn't work. It printed the same table 5 times.

kristen,
Cursor is slow and i'm trying to avoid it. That's why i wanna come out with some sort of loop. But my loop is not working.

What exactly are you trying to do? Your sample code makes no sense and neither does your requirements. Replacing a cursor but still using a loop won't help performance at all, in fact it may even be worse or more complicated. Why not step back and explain to us exactly what you have for data and what your goal is.

- Jeff
http://weblogs.sqlteam.com/JeffS

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2007-05-21 : 21:24:07

jsmith,

i want to insert the values from some tables to a new table.

i dont wanna do multiple insert...there are like 30 tables..

thats why i wanna pass a variable as an object and just do one insert.

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-22 : 00:23:01

To avoid cursor...declare a table with identity column and insert the table names do loop and fetch the table names as below...

declare @tt table(col1 int identity(1,1), table_name varchar(1000))

insert @tt(table_name)
select table_name from information_schema.tables
where table_name like 'table%'

find the count of table @tt and take the table name one by one using identity column, because it will be 1,2,3...etc(sequential)

david_reinjal
Starting Member

36 Posts

Posted - 2007-07-06 : 07:53:02

how can i do the same for MS Access?

pootle_flump

1064 Posts

Posted - 2007-07-06 : 08:27:04

quote:
Originally posted by david_reinjal

how can i do the same for MS Access?

It is more involved in Access and probably better asked in an Access forum.

Short answer -
Query MSysObjects where type = 1
or loop through the TableDef collection.

To actually do stuff with the table names you will need to use VBA. If you use the above query you will need to use it to populate and recordset and loop through that.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources