Select

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Select

Select

Rate Topic

Display Mode

Topic Options

Author

Message

soofy21

Posted Sunday, April 12, 2009 10:17 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, June 16, 2009 8:44 PM
Points: 6, Visits: 64

Hi,

Can any body let me know the query to select all the tables in the database with a particular word on it.

Thanks,
Soofy

Post #695477

ALZDBA

Posted Sunday, April 12, 2009 11:07 AM

SSCertifiable

Group: General Forum Members
Last Login: Monday, June 17, 2013 2:13 PM
Points: 6,866, Visits: 8,071

start with:

Select ...
from information_schema.tables
where table_name like '%yourcryteria%'

order by table_name

Johan

     Jul 13

Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help

- How to prevent a sore throat after hours of presenting ppt ?

"press F1 for solution", "press shift+F1 for urgent solution" BigGrin

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me Hehe

Post #695485

Husain_Kachwala

Posted Monday, April 13, 2009 6:03 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, October 12, 2011 6:03 AM
Points: 20, Visits: 55

You can also try the following:

use database_name

select * from sys.tables where type ='u'
and name like '%criteria%'

Post #695667

hammerheaded

Posted Monday, April 13, 2009 12:19 PM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, June 16, 2010 2:53 AM
Points: 264, Visits: 236

if you're stuck in a pre 2005 world you would do:

select * from sysobjects where type ='u'
and name like '%your_table_name%'

Post #696029

ALZDBA

Posted Monday, April 13, 2009 1:28 PM

SSCertifiable

Group: General Forum Members
Last Login: Monday, June 17, 2013 2:13 PM
Points: 6,866, Visits: 8,071

INFORMATION_SCHEMA views also work with pre sql2005 versions.

To only select actual tables you need to add the where clause

Where TABLE_TYPE = 'BASE TABLE'

Johan

     Jul 13

Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere w00t

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me Hehe

Post #696090

hammerheaded

Posted Monday, April 13, 2009 1:31 PM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, June 16, 2010 2:53 AM
Points: 264, Visits: 236

ALZDBA (4/13/2009)

INFORMATION_SCHEMA views also work with pre sql2005 versions.

To only select actual tables you need to add the where clause

Where TABLE_TYPE = 'BASE TABLE'

thx. i didn't mean to imply that it wouldn't.

Post #696092

Husain_Kachwala

Posted Monday, April 13, 2009 10:51 PM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, October 12, 2011 6:03 AM
Points: 20, Visits: 55

thnx for enlightening me. m still a novice...

Post #696304

WILLIAM MITCHELL

Posted Wednesday, April 15, 2009 6:11 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Yesterday @ 12:04 PM
Points: 1,442, Visits: 2,500

You could also use

sp_tables '%yourcriteria%'

Post #697432

« Prev Topic | Next Topic »

Permissions