USE NorthDynamic go SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Single-argument version of SanitizeObjectNameEx -- ============================================= CREATE FUNCTION [dbo].[SanitizeObjectName] ( @full_name nvarchar(804) ) RETURNS nvarchar(804) AS BEGIN DECLARE @full_nameS nvarchar(804) SET @full_nameS = [dbo].[SanitizeObjectNameEx](@full_name, DEFAULT, DEFAULT) RETURN @full_nameS END GO -- ============================================= -- Given the name of an object (such as a table, view, or stored procedure), returns the formal, fully qualified name. -- Thanks to Erland Sommarskog ( http://www.sommarskog.se/dynamic_sql.html#objnameparams ) -- ============================================= CREATE FUNCTION [dbo].[SanitizeObjectNameEx] ( @full_name nvarchar(804), -- Name of object, e.g. 'tablename', '#tablename', 'tempdb..#tablename', 'db.dbo.storedprocedure', etc. However, 'server.db.schema.table' is NOT allowed. @throwIfNotExist bit = 'false', -- Whether or not to require that the object exists. If parameter value is 1 (or 'true') and the object doesn't exist, an error will be thrown. @schema_default sysname = 'dbo' -- Schema name unless otherwise specified. Ignored if -- object_id(@full_name) IS NOT NULL (because existing objects already have a specified schema), -- parsename(@full_name, 2) IS NOT NULL (because the schema is specified within @full_name), or -- @throwIfNotExist <> 'true' (because otherwise an error is thrown if the object doesn't already exist) /* Currently (SQL Server 2019), the length of an object name might be as long as [sysname].[sysname].[sysname] which would be 392 characters (1 + 128 + 3 + 128 + 3 + 128 + 1). Doubling that and adding 20 extra characters for forward compatibility and padding gives us 804 */ ) RETURNS nvarchar(804) AS BEGIN DECLARE @obj_id int, -- object_id(@full_name) @db_id int, -- database containing the object (or current db if object doesn't exist) @db_nameS sysname, -- name of database, sanitized @schema_name sysname, -- name of schema, NOT sanitized @bit bit, -- Just a dummy variable, needed when using the Throw function. @full_nameS nvarchar(804) -- The return value! The sanitized, fully qualified name of the object. -- Supporting names on linked server would require a whole lot more work. IF parsename(@full_name, 4) IS NOT NULL SET @bit = [dbo].[Throw]('SanitizeObjectNameEx error: Tables on a different server are not supported. (''' + parsename(@full_name, 4) + ''' in ' + @full_name + ')') -- get @db_id IF (LEFT(@full_name,1) = '#') BEGIN -- Special handling for a string that's assumed to be simply a temp table name SET @full_name = 'tempdb..' + @full_name SET @db_id = db_id('tempdb') END ELSE IF parsename(@full_name, 3) IS NOT NULL -- Db is explicitly mentioned in the name SELECT @db_id = db_id(parsename(@full_name, 3)) ELSE -- Default to current db SELECT @db_id = db_id() SET @db_nameS = quotename(db_name(@db_id)) --sanitized database name SELECT @obj_id = object_id(@full_name) -- object id if exists IF @obj_id IS NOT NULL -- found the object! Return a sanitized version of the formal name. SELECT @full_nameS = @db_nameS + '.' + quotename(object_schema_name(@obj_id, @db_id)) + '.' + quotename(object_name(@obj_id, @db_id)) ELSE IF @throwIfNotExist = 'false' BEGIN -- Table didn't exist but caller explicitly said that was okay. -- Get the schema name SET @schema_name = parsename(@full_name, 2) IF @schema_name IS NULL SET @schema_name = @schema_default SELECT @full_nameS = @db_nameS + '.' + quotename(@schema_name) + '.' + quotename(parsename(@full_name, 1)) END ELSE -- object not found AND caller wants an error thrown if that's the case SET @bit = [dbo].[Throw]('SanitizeObjectNameEx error: No such object ''' + @full_name + '''') -- If we got here, success! RETURN @full_nameS END GO /****** Currently (SQL Server 2019) throwing nice errors within functions aren't allowed. We should all be embarrassed that if you want your functions to throw an error with a message, the current best practice is to craft an error string and try to convert it to a number. This creates a conversion error rather than a relevant type of error, but at least the error string is raised! Thanks to https://stackoverflow.com/a/9583534/7158380 Example: DECLARE @bit bit SET @bit = [dbo].[Throw]('Error message to be thrown') -- throws error message "Conversion failed when converting the nvarchar value 'Error message to be thrown' to data type int." ******/ CREATE FUNCTION [dbo].[Throw] ( @error NVARCHAR(MAX) ) RETURNS BIT AS BEGIN RETURN CAST(@error AS INT) END GO