Someone in their wisdom created a table called
select schema_name(schema_id) ,* from sys.tables where type = 'U' and name like '%user%'
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
usr.user as parameter
and I even went off and created a user defined data type just to test
question: is there a better(performance wise)\more standard way to get this done?