Need an efficient routine to pass object name, with or without "[" and "]" brackets, to get ObjectID

Question

Someone in their wisdom created a table called user:

select schema_name(schema_id)
,*
from sys.tables
where type = 'U'
 and name like '%user%'

enter image description here

 select top 100 *
 from usr.user

Msg 156, Level 15, State 1, Line 14 Incorrect syntax near the keyword 'user'.

I have many scripts where I pass the object name and I need to check where the name passed is valid (exists in the current database) or not.

some are failing when I pass the tablename usr.user as a parameter.

so I had a look as how sql server does this, using sp_help as an example

and I came out with something like this:

--=======================================================
-- check for the existence of object name or data type (system or user)
-- within the current database
-- works on sql server 2016 but probably lower versions too
-- marcelo miorelli
-- 18-july-2017
--=======================================================
SET NOCOUNT ON
declare @objname varchar(108) = NULL -- object name we're after 
declare @dbname varchar(108) = NULL -- object name we're after 
select @objname = 'ipv4' -- this is the parameter to be passed
-- Make sure the @objname is local to the current database. 
 select @dbname = parsename(@objname,3) 
 if @dbname is null 
 select @dbname = db_name() 
 else if @dbname <> db_name() 
 begin 
 raiserror(15250,-1,-1) 
 --return(1) 
 end 
 -- @objname must be either sysobjects or systypes: first look in sysobjects 
 declare @objid int 
 declare @sysobj_type char(2) 
 select @objid = object_id, 
 @sysobj_type = type 
 from sys.all_objects 
 where object_id = object_id(@objname) 
 -- IF NOT IN SYSOBJECTS, TRY SYSTYPES -- 
 if @objid is null 
 begin 
 -- UNDONE: SHOULD CHECK FOR AND DISALLOW MULTI-PART NAME 
 select @objid = type_id(@objname) 
 -- IF NOT IN SYSTYPES, GIVE UP 
 -- in SQL Server the system tables are deprecated (i.e. syscolumns, sysobjects) 
 -- and it's recommended as a best practice to use the views instead, 
 -- sys.columns, sys.objects, sys.types, etc 
 if @objid is null 
 begin 
 raiserror(15009,-1,-1,@objname,@dbname) 
 --return(1) 
 end 
end 
select [@objname] = @objname
 ,[@objid] = @objid
 ,[@sysobj_type]=@sysobj_type
 ,[@dbname]=@dbname

example with usr.[user] as parameter enter image description here

usr.user as parameter

enter image description here

and I even went off and created a user defined data type just to test ipv4

enter image description here

question: is there a better(performance wise)\more standard way to get this done?

Answer 1

Some notes:

1. You can get rid of PARSENAME and get the object name from your existing sys.objects query since you are using the OBJECT_ID() built-in function to handle getting the object regardless of how it is named.
2. Getting rid of PARSENAME means that you won't be handling Database names, but the comment at the top of your original code in the question does specify that you are only concerned about checking "within the current database".
3. You can then JOIN to sys.schemas to get the Schema name at the same time.
4. It is very easy to concatenate the SchemaName and ObjectName together, so leave them separate so that you can deal with the pieces if needed.
5. You do not need / want an IF block to check for Types since that ignores an edge case. Objects and Types are in two separate tables so there is no uniqueness enforced for names between them. Meaning, any schema-bound object residing in sys.objects can have the same name as a type in sys.types. You need to handle the case where an object and a type both have the same schema and name.
6. Schema names and Object / Type names should be wrapped in square brackets via the QUOTENAME built-in function.
7. There are four different types of Types: System, User-Defined Data Types (UDDT), SQLCLR User-Defined Types (UDT), and User-Defined Table Types (UDTT). You will probably need to distinguish between these as they aren't always handled the same way.

8. Always use NVARCHAR for names of anything in the system: object, schema, column, type, index, job, assembly, queue, database, login, server, etc, etc. Never use VARCHAR. Most names are defined as being the sysname type, which is just an alias (that lives in master but is available everywhere, similar to system stored procedures) for NVARCHAR(128).

   When dealing with a single-part name, use sysname (there are a few instances where sysname isn't being used, but this should generally always work since most names never get that large anyway). When dealing with multi-part names, you need to increase the size to account for each part plus the square brackets plus the connecting period / dot. Here we are handling SchemaName + ObjectName, so that is 128 for each "name" + 4 square brackets + 1 dot between them = 261 max characters. If you needed to also account for Database names, that would be another 131 characters (128 + 2 + 1).

   The reasons for using NVARCHAR instead of VARCHAR are:

   1. At the most basic level, this should be done for datatype consistency in your code. Since names are all internally NVARCHAR, using VARCHAR causes explicit conversions everywhere you those parameters / variables / columns (if storing names in tables).
   2. Since all of the names in the system are internally stored as NVARCHAR, it is technically ok to use any Unicode characters in any name, including combining characters, supplementary characters, etc. Yes, there is a note that supplementary characters don't work in Database names, but they actually ;-) (there are just some nuances to deal with in that case). So, if you use VARCHAR for parameters / variables, you could be a mangled version of the name and not the name itself, which will produce odd behavior.

The Inline Table-Valued Function (ITVF) below encapsulates all of the above suggestions. The system Types can be further broken down into base types (INT, VARCHAR, etc) and CLR types (HierarchyID, Geometry, and Geography) if need be, but here they are all just system "Types".

Inline-TVF

CREATE FUNCTION dbo.GetObjectInfo (@Name NVARCHAR(261))
RETURNS TABLE
--WITH SCHEMABINDING -- cannot schema bind due to referencing system objects
AS RETURN
SELECT obj.[object_id],
 QUOTENAME(sch.[name]) AS [SchemaName],
 QUOTENAME(obj.[name]) AS [ObjectName],
 obj.[type]
FROM sys.all_objects obj WITH (NOLOCK)
INNER JOIN sys.schemas sch WITH (NOLOCK)
 ON sch.[schema_id] = obj.[schema_id]
WHERE obj.[object_id] = OBJECT_ID(@Name)
UNION ALL
SELECT typ.[user_type_id],
 QUOTENAME(sch.[name]) AS [SchemaName],
 QUOTENAME(typ.[name]) AS [ObjectName],
 CASE typ.[is_user_defined]
 WHEN 1 THEN CASE
 WHEN typ.[is_assembly_type] = 1 THEN 'UDT'
 WHEN typ.[is_table_type] = 1 THEN 'UDTT'
 ELSE 'UDDT'
 END
 ELSE 'Type'
 END AS [type]
FROM sys.types typ WITH (NOLOCK)
INNER JOIN sys.schemas sch WITH (NOLOCK)
 ON sch.[schema_id] = typ.[schema_id]
WHERE typ.[user_type_id] = TYPE_ID(@Name);
GO

Tests

USE [tempdb];
CREATE TABLE dbo.[user] (Col1 INT);
CREATE TYPE dbo.[user] FROM INT NOT NULL;
CREATE TYPE dbo.[table] AS TABLE (Col1 INT);
SELECT * FROM dbo.GetObjectInfo(N'dbo.user'); -- returns 2 rows
SELECT * FROM dbo.GetObjectInfo(N'[dbo].[user]'); -- returns 2 rows
SELECT * FROM dbo.GetObjectInfo(N'dbo.table'); -- returns 1 row
SELECT * FROM dbo.GetObjectInfo(N'[dbo].[table]'); -- returns 1 row