Situatie
Mai jos este o procedura ce listeaza toate proprietatile extinse definite pe toate bazele de date de pe un server Microsoft SQL.
Solutie
alter PROC ListExtendedDBproperties
as
CREATE TABLE #PropList
(
class_desc varchar(50),
DBname varchar(100),
PropertyName varchar(100),
value sql_variant
)
DECLARE @dbname varchar(50)
DECLARE @cmd nvarchar(1000)
DECLARE c1 CURSOR FORWARD_ONLY FOR
SELECT name as DBname FROM sys.databases where state=0
— SELECT * FROM sys.databases
OPEN c1
FETCH c1 INTO @dbname
WHILE (@@fetch_status = 0)
BEGIN
SET @cmd = ‘INSERT INTO #PropList
(class_desc, DBname, PropertyName, value)
SELECT class_desc, ”’ + @dbname + ”’ as DBname,
name as PropertyName,
value
FROM [‘ + @dbname + ‘].sys.extended_properties
WHERE class = 0 ‘
EXEC (@cmd)
FETCH c1 INTO @dbname
END
CLOSE c1
DEALLOCATE c1
SELECT * FROM #PropList ORDER BY DBname
Leave A Comment?