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.
| Author |
Topic |
|
Largo
Starting Member
22 Posts |
 Posted - 2009-09-23 : 05:26:10
|
I execute following stored procedure to get all tables in my database.EXECUTE sp_tables @table_name = '%', @table_type = 'table'; However, no tables appear in result. I tried to filter by owner - and it works.So, here are questions:1) Why @table_type doesn't work?2) How to iterate thru all tables in database?Thanks beforehand!!!-----There is no knowledge that is not power. |
|
|
Kabila
Starting Member
33 Posts |
Posted - 2009-09-23 : 06:30:42
|
| EXECUTE sp_tables @table_name = '%', @table_type = "'Table'";iterate thru all tables in databaseEXECUTE sp_tables @table_name = '%', @table_type = "'Table'",@table_qualifier = DataBasename; |
 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-09-23 : 06:53:47
|
[code]SELECT *FROM INFORMATION_SCHEMA.TABLES[/code]
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
Largo
Starting Member
22 Posts |
Posted - 2009-09-23 : 08:03:46
|
| Kabila! Your code doesn't work. I see you enclosed "table" into single quotes, but it's of no help.When I added @table_owner = 'dbo', then all went fine.Khtan! Your query returns the same set as EXECUTE sp_tables. :(-----There is no knowledge that is not power. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2009-09-23 : 08:09:09
|
| <<2) How to iterate thru all tables in database?>>What do you mean by that?MadhivananFailing to plan is Planning to fail |
|
|
|
Kabila
Starting Member
33 Posts |
Posted - 2009-09-23 : 08:36:55
|
| This Code works definetlyEXECUTE sp_tables @table_name = '%', @table_type = "'Table'";Your Code will return only the table having schema name 'DBO'@table_owner = 'dbo' |
|
|
|
Largo
Starting Member
22 Posts |
Posted - 2009-09-24 : 00:08:58
|
| Kabila! By iterating thru all tables I mean showing all MY tables in database.Yesterday your code didn't work, but today all goes fine. Nuff strange. :(-----There is no knowledge that is not power. |
|
|
|
|
|
|
|
|
Iterating tables problem