question

jctronicsshiva avatar image
jctronicsshiva asked

SQL extended proeprties?

Lets say i have 3 databases of type Device(might be three different versions),which will have different DB names like (Device1, Device2, Device3). Now i want to query the server and get all databases of type Device, which should return the above 3 dbs. How do i do it ? I thought of adding a extended property in DB schema. By adding name = type, and value= "Device" and querying this back? What's the best mechanism to do it? and if possible can someone give the query too. Can i add version info also in the extended property?
sql-server-2005t-sqlquerydatabasescript
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
If any of the answers are helpful, please indicate this by clicking on the thumbs up next to them. If any one answer solves your question, indicate that by clicking on the check mark next to that answer. You can vote on as many helpful answers as you want. You can only select one correct answer.
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
To query the extended properties of the databases on the server you would have to run the query against each database on the server. The only way to do that is through a cursor of some type. You can use the one that Microsoft supplies, sp_msforeachdb. It's not a documented procedure but if you search on the internet you can see how to use it. It will do what you need.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

eghetto avatar image
eghetto answered
What kind of information are you looking for? To find the databases you can just use: SELECT * FROM sys.databases WHERE databases.name LIKE N'Device%' To add an extended property use: EXEC [Device1].sys.sp_addextendedproperty @name=N'Version', @value=N'1';
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

jctronicsshiva avatar image jctronicsshiva commented ·
hmm thanks for the reply. This is not what exactly i am looking for.. I want to get the list of databases of type 'Device' by checking the extended property and not the DB name. DB name can be anything.I want to have an extended property storing the DB type and while retrieving back the query should filter it based on the extended property.
0 Likes 0 ·
eghetto avatar image
eghetto answered
Based on Grant's idea: IF OBJECT_ID('tempdb..##DeviceDB') IS NULL CREATE TABLE ##DeviceDB (DbName NVARCHAR(128)); EXEC dbo.sp_MSforeachdb @command1 = N' INSERT INTO ##DeviceDB SELECT DISTINCT DB_NAME() AS [DB_Name] FROM sys.extended_properties WHERE extended_properties.class = 0 AND extended_properties.name = N''Type'' AND extended_properties.value = N''Device'''; SELECT ##DeviceDB.DbName FROM ##DeviceDB; IF OBJECT_ID('tempdb..##DeviceDB') IS NOT NULL DROP TABLE ##DeviceDB
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.