executing ::fn_listextendedproperty from master

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

executing ::fn_listextendedproperty from master

Author

Topic

Michael1047
Starting Member

3 Posts

Posted - 2002-08-05 : 15:38:36

Team,

How can we collect database's extended property w/o connecting to it, this doesn't work:
Use Master
BEGIN
Declare @DBVersion NVARCHAR(11)

Declare @mydb SYSNAME
SET @mydb = 'MyDatabase'

BEGIN
EXEC('Use ' + @mydb)

SELECT @DBVersion = CAST(VALUE AS VARCHAR) FROM ::fn_listextendedproperty('DBVersion', NULL, NULL, NULL,NULL,NULL, NULL)
print '>' + @dbversion + '<'
end
END

Any Ideas? @dbVersion must be set. w/o connecting to the database directly. it would be cool if we could do this:
Set @dbVersion = @dbname::fn_listextendedproperty('DBVersion', NULL, NULL, NULL, NULL, NULL, NULL)

Michael

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-08-05 : 18:05:05

In the same way you cannot enumerate the tables in a database without connecting to it, how do you think it would be possible to enumerate the extended properties of the objects in a database without connecting to it ? It's not possible. Why can you not connect to the database ? Since extended properties are database specific and stored in the sysproperties table in each database you need to connect to the database in order to read it.

HTH
Jasper Smith

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources