Proper query

Question

Proper query

Igor Korot 56

Hi, ALL,

Right now I'm using following query

SELECT object_id FROM sys.objects o, sys.schemas s WHERE s.schema_id = o.schema_id AND o.name = ? AND s.name = ?

Is there a simple way to bring in sys.databases table?

Thank you.

EDIT:

Let me clarify this further as it seems the way its written is confusing...

When I connect to the DB thru the ODBC/C++ I have a database to connect to.

After that it is possible to bring in another database on same connection - either by using ATTACH or from CREATE DATABASE (as I think after it is created I will automatically be connected to it).

So, lets say I ran SQLDriverConnect() and got a successful connection. The DB name is "current_draft". Now later on I ran SQLExecDirect( stmt, "ATTACH last_year_draft".

When I run the "ATTACH..." command which, presumably succeed I need to retrieve the table info from that newly attached DB.

The function signature is:

GetTableId(const std::wstring &catalog, const std::wstring &schema, std::wstring &table, long &tableId, std::vectorstd::wstring &errors)

I hope it is now better and I get a good respnce on what and how to modify my query

Thank you.

LiHongMSFT-4306 31,566 Reputation points

2025-01-16T02:48:29.9266667+00:00

Hi @Igor Korot

Do you have further question on this issue?

If any answer is helpful to your question, please accept it as an answer so that other users with similar questions can see it more easily. :)
Igor Korot 56 Reputation points

2025-01-16T18:55:17.5333333+00:00

@LiHongMSFT,

Please check the edit to the OP.

Thx.
Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
Igor Korot 56 Reputation points

2025-01-16T20:43:34.34+00:00

I think this belongs to a different thread....

3 answers

Your answer

LiHongMSFT-4306 31,566 Reputation points

2025-01-16T02:48:29.9266667+00:00

Hi @Igor Korot

Do you have further question on this issue?

If any answer is helpful to your question, please accept it as an answer so that other users with similar questions can see it more easily. :)
Igor Korot 56 Reputation points

2025-01-16T18:55:17.5333333+00:00

@LiHongMSFT,

Please check the edit to the OP.

Thx.
Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
Igor Korot 56 Reputation points

2025-01-16T20:43:34.34+00:00

I think this belongs to a different thread....

Answer 1

博雄胡 685

easy

SELECT object_id,apply1.* FROM sys.objects o, sys.schemas s cross apply(select * from sys.databases d where d.database_id = db_id())apply1 WHERE s.schema_id = o.schema_id AND o.name = ? AND s.name = ?

Igor Korot 56 Reputation points

2025-01-15T13:11:15.4233333+00:00

Thx, Could you please explain this solution? What is apply1? And how it will correspond to my current query?
Igor Korot 56 Reputation points

2025-01-15T15:25:55.58+00:00

Hi,

I was also hoping to include that table in the FROM/WHERE clause of my query finding the table id for catalog.schema.table.

Thank you

博雄胡 685

Could you please explain this solution

Read Dan Guzman's explanation

What is apply1

This is Subqueries. Reference: https://learn.microsoft.com/en-us/sql/relational-databases/performance/subqueries?view=sql-server-ver15

I was also hoping to include that table in the FROM/WHERE clause of my query finding the table id for catalog.schema.table

I assume you want to find out which database a particular table is in and what schema it has under that database, so I wrote a query to help you do that

declare @N nvarchar(500) = N'userinfo'
,@databasename sysname 
,@schemaname sysname
,@tablename sysname
if len(@N) - len(replace(@N,'.',''))=2
begin
set @databasename = SUBSTRING(@N,1,CHARINDEX('.',@N)-1)
set @schemaname = SUBSTRING(@N,charindex('.',@N)+1,len(@n)-charindex('.',REVERSE(@n))-charindex('.',@N))
set @tablename = REVERSE(SUBSTRING(REVERSE(@N),1,CHARINDEX('.',REVERSE(@N))-1))
declare @sql1 nvarchar(max) = N'use '+@databasename+'
								select 
								DatabaseName = @databasename
								,SchemaName = a2.name
								,TableName = a1.name
								,TableName_objectID = a1.object_id
								from 
								sys.objects as a1
								inner join sys.schemas as a2 on a2.schema_id = a1.schema_id
								where 1=1
								and a1.name = @tablename
								and a2.name = @schemaname
								and a1.type = ''U'''
execute sp_executesql @sql1
,N'@databasename sysname,@schemaname sysname,@tablename sysname'
,@databasename,@schemaname,@tablename
end
if len(@N) - len(replace(@N,'.',''))=1
begin
set @schemaname = SUBSTRING(@N,1,CHARINDEX('.',@N)-1)
set @tablename = SUBSTRING(@N,CHARINDEX('.',@N)+1,128)
	if OBJECT_ID('tempdb..#t1') is not null drop table #t1 select * into #t1 from (select name,row=ROW_NUMBER()over(order by (select 1)) from sys.databases)T
	if OBJECT_ID('tempdb..##t1') is not null drop table ##t1 create table ##t1 (DatabaseName sysname,SchemaName sysname,TableName sysname,TableName_objectID bigint)
	declare @i int = (select count(*) from #t1)
	while @i>0
	begin
	set @databasename = (select name from #t1 where #t1.row = @i)
	declare @sql2 nvarchar(max) = N'use '+@databasename+'
									insert into ##t1 
									select 
									DatabaseName = @databasename
									,SchemaName = a2.name
									,TableName = a1.name
									,TableName_objectID = a1.object_id
									from 
									sys.objects as a1
									inner join sys.schemas as a2 on a2.schema_id = a1.schema_id
									where 1=1
									and a1.name = @tablename
									and a2.name = @schemaname
									and a1.type = ''U'''
	execute sp_executesql @sql2
	,N'@databasename sysname,@schemaname sysname,@tablename sysname'
	,@databasename,@schemaname,@tablename
	set @i = @i-1
	end
	select * from ##t1
	drop table ##t1
end
if len(@N) - len(replace(@N,'.',''))=0
begin
set @tablename = @N
	if OBJECT_ID('tempdb..#t2') is not null drop table #t2 select * into #t2 from (select name,row=ROW_NUMBER()over(order by (select 1)) from sys.databases)T
	if OBJECT_ID('tempdb..##t2') is not null drop table ##t2 create table ##t2 (DatabaseName sysname,SchemaName sysname,TableName sysname,TableName_objectID bigint)
	declare @i2 int = (select count(*) from #t2)
	while @i2>0
	begin
	set @databasename = (select name from #t2 where #t2.row = @i2)
	declare @sql3 nvarchar(max) = N'use '+@databasename+'
									insert into ##t2
									select 
									DatabaseName = @databasename
									,SchemaName = a2.name
									,TableName = a1.name
									,TableName_objectID = a1.object_id
									from 
									sys.objects as a1
									inner join sys.schemas as a2 on a2.schema_id = a1.schema_id
									where 1=1
									and a1.name = @tablename
									and a1.type = ''U'''
	execute sp_executesql @sql3
	,N'@databasename sysname,@tablename sysname'
	,@databasename,@tablename
	set @i2 = @i2-1
	end
	select * from ##t2
	drop table ##t2
end

博雄胡 685 Reputation points

2025-01-16T10:02:35.9366667+00:00

I forgot to say, @N can fill in catalog.schema.table or schema.table or table
Igor Korot 56 Reputation points

2025-01-16T18:56:50.68+00:00

Hi,

Please check my edit to the OP.

Answer 2

Dan Guzman 9,401

The sys.objects and sys.schemas catalog views are scoped to the current database (only objects in the current database context are returned). Simply add DB_NAME() to the result to return the current database name in the result set. No additional joins are needed. The below example uses the newer ANSI-92 explicit join syntax:

SELECT object_id, DB_NAME() AS database_name

FROM sys.objects o

JOIN sys.schemas s ON s.schema_id = o.schema_id

WHERE o.name = ? AND s.name = ?;

Igor Korot 56 Reputation points

2025-01-15T19:26:23.17+00:00

@Dan Guzman ,

And what if I need to work with multiple DB?

There is a reason I want to bring the catalog into the query - the only way to positively identify the table is through the catalog.schema..table.

Thank you.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2025-01-15T22:25:05.7266667+00:00

And what if I need to work with multiple DB?

Well, you only said that you wanted to bring in sys.databases, not exactly what you wanted it for. So Dan had to guess.
Igor Korot 56 Reputation points

2025-01-16T18:58:20.1333333+00:00

@Dan Guzman ,

Please check my edit to the OP...

Answer 3

Erland Sommarskog 121.4K MVP Volunteer Moderator

If I understand your edit to the post, you simply want to run

SELECT object_id FROM nameofdbhere.sys.objects o, nameofdbhere.sys.schemas s WHERE s.schema_id = o.schema_id AND o.name = ? AND s.name = ?

The query would need to have the DB name hardcoded, but since you seem to generate this query from the client, that should not be an issue.

Just have an unnerving feeling that I still don't understand your problem...

Igor Korot 56 Reputation points

2025-01-16T19:00:28.47+00:00

@Erland Sommarskog ,

Please check my edit to the OP..

Is dynamic query a solution to what I want to achieve?

Thx,
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2025-01-16T21:41:51.1633333+00:00

I've edited my answer - or rather, I replaced it entirely.
Igor Korot 56 Reputation points

2025-01-17T00:26:52.6733333+00:00

@Erland Sommarskog ,

I was hoping to parametrize the database/catalog name. But if I understand what your wrote correctly - it is not possible.

And no - I'm not building the query myself...

Thank you.

Share via

Proper query

3 answers

Your answer