Blog Post

Ambiguities and the wild object.oriented brigades

,

--Whilst trying to design a more effective way of detecting dependencies in a SQL Server database, I was mulling over the cruelty of SQL Server's parser in allowing this to compile and run..
create schema int
create table int
.int (int int)
select int.int.int int from int.int
--or
create schema character
create table character.character (character character)
select character.character character from character.character

/*
Yes, it runs perfectly happily. INT isn't even a reserved word. You can get up to all sorts of mischief. In the first example, we have ended up with a schema called int, a table called int and an integer column in that table called int.

Now, imagine that you are using SQL Search, or my splendid SQL expression for doing searches, to track down references to an entity such as a column name. This isn't easy, because all these objects will have the same name. No worries, you say, because you can always give an entity its full path. Attaching a schema and maybe a parent 'object' name should guarantee uniqueness. Hmmm. Up to a point. For a start, we don't always qualify column names, though it is a good idea. Secondly, some of us use stored procedures that access different objects sepending on the context they are executed in. Thirdly, there are some ambiguities now in TSQL that have been iinadvertently been introduced by the wild object-oriented brigades of CLR and XML.

Ambiguous references are one of the stumbling blocks to being able to reliably tease-out dependencies. */

--so let's now create a simple function
CREATE FUNCTION int.bogus()
RETURNS int AS
BEGIN return
(select top 1 int.int.int int from int.int) END
go
--now prime the pump
Insert into int.int (int) select 1 int

---we call it
SELECT int.bogus() FROM int.int int;

/*
---------------
1

(1 row(s) affected) */

/* We'll pop it into a stored procedure */

create procedure int.character
as
SELECT int
.bogus() FROM int.int int;
go

/* Now, it has correctly worked out that you actually wanted the inline function . However, what would have happened if your 'int' column, instead of being an INT, was a UDT column named int with a method named bogus()? What, on the other hand, the int.bogus() was actually an xquery reference to a column of type xml*/
/* Yessir, without getting too deep, we would than have an ambiguous reference. If you start putting that into routines, you'll end up making it very difficult to work out your dependencies since SQL Server will have to try to work out what you mean at run-time. This is an ambiguous reference. Now do you have to actually have two or more objects in existence that create an anbiguity, or is it just sufficient to create a reference that has a potential to be ambiguious if you were foolish enough to create competing objects with the same name? It looks as the latter is the case. */

SELECT referencing_schema_name+'.'+referencing_entity_name
FROM sys.dm_sql_referencing_entities ('int.bogus', 'OBJECT');
GO

/*
---------------------
int.character

(1 row(s) affected)
*/

--find all referenced objects that are ambiguous.
select referenced_schema_name+'.'+referenced_entity_name
from sys.sql_expression_dependencies
where is_ambiguous=1

/*
---------------------
int.bogus

(1 row(s) affected)
*/

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating