Need to Filter Query by Schema

Question

Need to Filter Query by Schema

Welsh Corgi

SSC Guru

Points: 116520
More actions
April 9, 2012 at 7:42 am

#259726

I need to Need to filter the following Query by Schema:
IF EXISTS (
SELECT * FROM tempdb.dbo.sysobjects AS obj
WHERE obj.xtype in ('U')
AND obj.id = object_id(N'tempdb.. #Cols')
)
DROP TABLE #tblDBObjects;
CREATE TABLE #Cols
(
DataBaseName varchar(128),
TableName nvarchar(128),
Columnname varchar(128),
xtype tinyint,
length int
)
EXECUTE master.sys.sp_MSforeachdb 'USE [?]; insert #Cols SELECT DB_NAME(), obj.Name AS TableName,col.Name AS ColumnName, col.xtype, col.length
FROM Sysobjects AS obj
INNER JOIN syscolumns AS col ON obj.id = col.id
WHERE Obj.xtype = ''U''
AND col.name LIKE ''%Source_Code%''
--AND col.name LIKE ''%type%''
-- AND col.Name LIKE ''%'''
SELECT * FROM #Cols
DROP TABLE #Cols
What do I need to JOIN, etc to accomplish this?
Thanks.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Syscolumns and sysobjects are deprecated, should not be used any longer, included only for backward compat with SQL 2000

Use sys.objects and sys.columns and filter on the schema_id (or join to sys.schemas and filter on the name)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 2

Yeah, thanks for pointing that out.

What Table.Column should I JOIN ON?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

R.P.Rozema SSChampion Points: 12317 More actions · Answer 3

You seem to be in a real hurry for an answer, but maybe you should reread your question before becoming aggitated. What do you mean to say with "filter the following Query by Schema". I have no idea what you are trying accomplish from just that oneliner, please be more elaborate in describing your needs.

Have you had a look at the documentation for sys.columns, sys.tables and sys.objects as was suggested? My best guess for what I can make of your question is that you are looking for sys.objects.schema_id which you can join onto sys.schemas.schema_id. Also the function object_schema_name() may be of use to you, albeit that this for sure is not going to be the fastest method if you use that as a filter criterium.

Posting Data Etiquette - Jeff Moden[/url]
Posting Performance Based Questions - Gail Shaw[/url]
Hidden RBAR - Jeff Moden[/url]
Cross Tabs and Pivots - Jeff Moden[/url]
Catch-all queries - Gail Shaw[/url]

If you don't have time to do it right, when will you have time to do it over?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

GilaMonster (4/9/2012)
Use sys.objects and sys.columns and filter on the schema_id (or join to sys.schemas and filter on the name)

If you're asking about the join column to sys.schemas, the full definition is in Books Online, or if you want the slow answer, on schema_id.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 5

R.P.Rozema (4/9/2012)
You seem to be in a real hurry for an answer, but maybe you should reread your question before becoming aggitated.

Who is agitated?:-)

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Books Online is your friend. It can answer many of your basic questions faster than posting on ssc. If still confused, then ssc is a good place for getting clarification.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 7

Lynn Pettis (4/9/2012)
Books Online is your friend. It can answer many of your basic questions faster than posting on ssc. If still confused, then ssc is a good place for getting clarification.

You are right. I used to rely heavily on BOL, more so with SQL Server 2000.

I got a lot of bad code thrown at me late Thursday Night and it was kinda nightmarish but that is not a valid excuse.

Fortunately I was able to reject the bad code and things are looking a lot better.

Yeah look it up in BOL, lazy, I agree and I know that I unintentially annoyed several people.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Need to Filter Query by Schema

Cookies on SQLServerCentral