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?
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.
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.
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';
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.
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
15 People are following this question.
