loop through a get all 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

General SQL Server Forums

New to SQL Server Programming

loop through a get all table names

Author

Topic

smorty44
Yak Posting Veteran

93 Posts

Posted - 2008-03-12 : 10:56:18

I'd like to loop through a database and get all the user table names and insert them into another table. What's the best way to do this without using a cursor?
This gives me the last table only.

declare @table_name char(50)

select @table_name = object_name(id) from sysindexes where indid =0

INSERT INTO holding.dbo.tbl_inputfiles
(IP_File, IP_Act, IP_Import)
Values (@table_name,'Y','ADV')

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-03-12 : 10:57:50

[code]INSERT INTO holding.dbo.tbl_inputfiles (IP_File, IP_Act, IP_Import)
select object_name(id), 'Y', 'ADV'
from sysindexes
where indid = 0[/code]

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-12 : 10:58:38

INSERT INTO holding.dbo.tbl_inputfiles (IP_File, IP_Act, IP_Import)
select table_name, 'Y', 'ADV' from information_schema.tables

E 12°55'05.25"
N 56°04'39.16"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-03-12 : 11:00:52

INFORMATION_SCHEMA.TABLES is a better source

KH
[spoiler]Time is always against us[/spoiler]

smorty44
Yak Posting Veteran

93 Posts

Posted - 2008-03-12 : 11:10:52

Thank you, this works well. In using information_schema.tables is there anyway to exclude system tables?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-12 : 11:23:30

[code]INSERT Holding.dbo.tbl_InputFiles
(
IP_File,
IP_Act,
IP_Import
)
SELECT TABLE_NAME,
'Y',
'ADV'
FROM INFORMATION_SCHEMA.TABLES
WHERE OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'IsUserTable') = 1
AND OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'IsSystemTable') = 0
AND OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'IsMSShipped') = 0
AND TABLE_NAME NOT IN ('sysdiagrams')
AND TABLE_TYPE = 'BASE TABLE'[/code]

E 12°55'05.25"
N 56°04'39.16"

X002548
Not Just a Number

15586 Posts

Posted - 2008-03-12 : 11:24:40

quote:
Originally posted by smorty44

Thank you, this works well. In using information_schema.tables is there anyway to exclude system tables?

You see system tables?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-03-12 : 11:25:53

System tables is not included in INFORMATION_SCHEMA however view is included.

Refer to BOL http://msdn2.microsoft.com/en-us/library/ms186224.aspx for more information

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources