Searching for Tables Only in T-SQL

NigelRocks
NigelRocks used Ask the Experts™
on
Experts,

The query below is meant to only return tables with the name "Factor" in them, but I'm getting stored procs back as well.  How do filter out the stored procs and only get tables?
DECLARE @ObjectName varchar(40)
SET @ObjectName = '%Factor%'
 
select o.[name] databasename, o.[name] tablename,c.[name] columnname from syscolumns c,sysobjects o
where o.id = c.id 
AND o.[name] LIKE @ObjectName 
ORDER BY tableName

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
add this condition:

o.xtype = 'u'

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Instead you can do:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_TYPE = 'BASE TABLE'

This should give you the desired results
select o.[name] databasename, o.[name] tablename,c.[name] columnname from syscomments c,sysobjects o
where o.id = c.id
AND c.TEXT  LIKE @ObjectName
ORDER BY tableName

try this
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

Author

Commented:
angellll, where do I find the other codes for objects?  In other words, what if I only want to see stored procs next time?
rrjegan17, TABLE_TYPE is not recongized.
 
 
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
http://www.devx.com/tips/Tip/14107
C = CHECK constraint 
D = Default or DEFAULT constraint 
F = FOREIGN KEY constraint 
L = Log 
P = Stored procedure 
PK = PRIMARY KEY constraint (type is K) 
RF = Replication filter stored procedure 
S = System table 
TR = Trigger 
U = User table 
UQ = UNIQUE constraint (type is K) 
V = View 
X = Extended stored procedure 

Open in new window

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
A typo:

SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

Author

Commented:
rrjegan17,
Using your method, how would I only get back stored procedures, etc. like angellll's post shows for the other method?
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
To get back stored procedures, you need to use

SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE'

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial