CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sqlstored-procedures
dance2die
dance2die

Reputation: 36915

What does "%%DatabaseEx" do in TSQL?

I was looking at the source of sys.sp_dbcmptlevel in SQL Server 2005.

In the source, there is this line I do not understand how it works.

EXEC %%DatabaseEx(Name = @dbname).SetCompatibility(Level = @input_cmptlevel)

It doesn't appear that DatabaseEx is a stored procedure.

-- does not return any result
select  *
from    sys.procedures
where   [name] like '%DatabaseEx%'

So my questions are

Upvotes: 9

Views: 1569

Answers (3)

Mike Dimmick
Mike Dimmick

Reputation: 9802

I think the best answer here is that it's not documented, and not supported, so don't rely on it. While it's interesting to know how SQL Server works internally, anything you do with that knowledge has the potential to break in a future hotfix, service pack or release.

Upvotes: 7

devio
devio

Reputation: 37215

Interesting find.

System SP's also refer to %%Object, %%Relation, %%ColumnEx, %%LinkedServer, %%Owner, %%CurrentDatabase(), %%ErrorMessage, %%Module, %%DatabaseRef, %%LocalLogin, %%Alias, %%ServerConfiguration, %%IndexOrStats, %%ScalarType (etc)

My interpretation is that the %%() retrieves some kind of (COM?) object based on filter criteria, followed by a method call.

Upvotes: 3

KM.
KM.

Reputation: 103587

-- Note: database @dbname may not exist anymore
-- Change compatibility level
-- If invoke gets error, exception will abort this proc.
EXEC %%DatabaseEx(Name = @dbname).SetCompatibility(Level = @input_cmptlevel)

it looks like a way to refer to a variable database as an object and make config changes

Upvotes: 2

Related Questions