Getting tables and fields from a database

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)

Getting tables and fields from a database

Author

Topic

junaidKhan
Starting Member

3 Posts

Posted - 2008-12-27 : 06:57:45

Hi
I want to see whether 'someString' appears in the database.
the database contains 100s of tables, and each tables contains 1000s
rows and many columns.

for this I wnat to run 2 for loops, one for tables in the databse
and another for columns in the tables.

Ok, getting the columns is easy, but how can I get the tables collection in the database?

any idea

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2008-12-27 : 07:35:26

select * from sys.tables

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-27 : 07:56:56

i think ur requirement, is to get a column name in a particular table in a database

try this

select table_name,column_name from information_Schema.columns where column_name like '%columnname%'

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-12-27 : 08:58:44

check this link
http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm

"There is only one difference between a dream and an aim.
A dream requires soundless sleep to see,
whereas an aim requires sleepless efforts to achieve..!!"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-27 : 11:33:44

or use undocumented procedures sp_msforeachdb and sp_msforeachtable

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-01-15 : 11:13:11

quote:
Originally posted by visakh16

or use undocumented procedures sp_msforeachdb and sp_msforeachtable

Wow, thanks for those. I've never heard of them before and they seem very useful. There's a great writeup about them here:
http://www.databasejournal.com/features/mssql/article.php/3441031/SQL-Server-Undocumented-Stored-Procedures-spMSforeachtable-and-spMSforeachdb.htm

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-01-15 : 11:14:25

Oops, I didn't realize I bumped up an old thread. Sorry about that - I got carried away reading through some older stuff.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources