Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Select Expand / Collapse
Author
Message
Posted Sunday, April 12, 2009 10:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Sunday, April 12, 2009 11:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 12:40 AM
Points: 7,001, Visits: 8,439
start with:
Select ...
from information_schema.tables
where table_name like '%yourcryteria%'

order by table_name



Johan


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

- 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"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #695485
Posted Monday, April 13, 2009 6:03 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Monday, April 13, 2009 12:19 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC 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
Posted Monday, April 13, 2009 1:28 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 12:40 AM
Points: 7,001, Visits: 8,439
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


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

- 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"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #696090
Posted Monday, April 13, 2009 1:31 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC 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
Posted Monday, April 13, 2009 10:51 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Wednesday, April 15, 2009 6:11 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Saturday, July 26, 2014 6:57 AM
Points: 1,502, Visits: 2,670
You could also use

sp_tables '%yourcriteria%'

Post #697432
Posted Wednesday, May 7, 2014 3:05 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 4, 2014 4:04 AM
Points: 3, Visits: 7
select * from sys.tables


http://www.tutorialesql.com/
Post #1568704
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse