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?
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';
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