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

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()  

 -- @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 @objid is null  
  select @objid = type_id(@objname)  

  -- 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  

select [@objname] = @objname
      ,[@objid] = @objid

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?


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".


CREATE FUNCTION dbo.GetObjectInfo (@Name NVARCHAR(261))
--WITH SCHEMABINDING -- cannot schema bind due to referencing system objects
SELECT obj.[object_id],
       QUOTENAME(sch.[name]) AS [SchemaName],
       QUOTENAME(obj.[name]) AS [ObjectName],
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)
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'
         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);


USE [tempdb];
CREATE TABLE dbo.[user] (Col1 INT);
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
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.