According to the database theory, any table inside a database is successfully identified by using its fully qualified name, catalog_name.schema_name.table_name.
In SQL Server a way to retrieve the table id is:
SELECT object_id(table_name);
Should I use the fully qualified table name from the first sentence as a parameter to this query? If not - how the engine will know what table I'm requesting this for?
If you are in the same database as the object, you can use schema qualified name.
E.g., Schemaname.ObjectName
USE AdventureWorks2012;
GO
SELECT OBJECT_ID(N'Production.WorkOrder') AS 'Object ID';
GO
If you are in different database, as against the object database, you have to use fully qualified name of the object. E.g. DatabaseName.SchemaName.ObjectName
USE master;
GO
SELECT OBJECT_ID(N'AdventureWorks2012.Production.WorkOrder') AS 'Object ID';
GO
Basically, it retrieves information from sys.objects catalog view and returns the object identifier. You can read more about this: object_id
An old but still relevant article on name resolution in SQL Server:
For this purpose in sqlserver have a Master database in which all other database and its all objects entry in there and it store in system tables.
For each object in the master database is define from Object_ID, there are different tables. Sys.objects is the table which has all the entry which differ via type (like U- User defined, T -
There are various ways you can find all table names present in a database by using following system or metadata tables in SQL server (refer Quara link)
Each tables you can access from catalog_name.schema_name.table_name
Here is
catalog_name - is your database name
schema_name - is your user / Schema name(for access rights, by default its dbo and have all permission)
table_name - is your actual table name
How can I find tables name of selected database in MsSql
Anything start from Sys in db is the reserve and system object either table or view or procedure or else.