public static List<OSBBDatabase> GetOSBBDatabases(SqlConnection connection)
{
const string SELECT_OSBB_DBS_QUERY = @"
DECLARE @dbname sysname,
@sql nvarchar(1000),
@dbid int
SET NOCOUNT ON
CREATE TABLE #gsysobjects(dbname sysname,
DbID int, id int,
[name] sysname,
NameDB varchar(50))
CREATE TABLE #isexist(isexist int)
DECLARE curDB CURSOR FOR
SELECT [Name],
DbID
FROM master..sysdatabases
WHERE [Name] NOT IN ('master','tempdb','pubs','msdb','model')
and HAS_DBACCESS([Name]) = 1
OPEN curDB
FETCH NEXT FROM curDB
INTO @dbname, @dbid
WHILE @@FETCH_STATUS = 0
BEGIN
DELETE #isexist
SET @sql =
'IF EXISTS(SELECT * FROM [' +
@dbname+'].dbo.sysobjects s JOIN [' +
@dbname+'].dbo.syscolumns c ON c.id = s.id AND c.name = ''DBName'' ' +
' WHERE s.xtype = ''u'' AND s.name = ''rp_DBProperties'' ) ' +
' INSERT INTO #isexist(isexist) VALUES(1) '
PRINT @sql
EXEC(@sql)
IF EXISTS(SELECT * FROM #isexist)
BEGIN
SET @sql = ' INSERT INTO #gsysobjects (dbname, dbid, id, name, namedb) SELECT ' +
''''+ @dbname + '''' + ',' + CAST(@dbid as varchar)+', id, name, DBName FROM [' +
@dbname+'].dbo.sysobjects, ['+ @dbname +
'].dbo.rp_DBProperties WHERE xtype = ''u'' AND name = ''rp_DBProperties'''
PRINT @sql
EXEC (@sql)
END
FETCH NEXT FROM curDB INTO @dbname, @dbid
END
CLOSE curDB
DEALLOCATE curDB
SELECT dbname + CASE WHEN namedb is null then '' else ' ' + namedb END DBS, *
FROM #gsysobjects
order by dbname
DROP TABLE #gsysobjects
DROP TABLE #isexist
SET NOCOUNT OFF
";
List<OSBBDatabase> result = new List<OSBBDatabase>();
SqlCommand command = new SqlCommand(SELECT_OSBB_DBS_QUERY, connection);
command.CommandType = CommandType.Text;
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
OSBBDatabase db = new OSBBDatabase()
{
Alias = (string)reader["DBS"],
DatabaseID = (int)reader["dbname"],
DBName = (string)reader["DbID"]
};
result.Add(db);
}
return result;
}
public static List <OSBBDatabase> GetOSBBDatabases(SqlConnection connection)
{
const string SELECT_OSBB_DBS_QUERY = @"
DECLARE @dbname sysname,
@sql nvarchar(1000),
@dbid int
SET NOCOUNT ON
CREATE TABLE #gsysobjects(dbname sysname,
DbID int, id int,
[name] sysname,
NameDB varchar(50))
CREATE TABLE #isexist(isexist int)
DECLARE curDB CURSOR FOR
SELECT [Name],
DbID
FROM master..sysdatabases
WHERE [Name] NOT IN ('master','tempdb','pubs','msdb','model')
and HAS_DBACCESS([Name]) = 1
OPEN curDB
FETCH NEXT FROM curDB
INTO @dbname, @dbid
WHILE @@FETCH_STATUS = 0
BEGIN
DELETE #isexist
SET @sql =
'IF EXISTS(SELECT * FROM [' +
@dbname+'].dbo.sysobjects s JOIN [' +
@dbname+'].dbo.syscolumns c ON c.id = s.id AND c.name = ''DBName'' ' +
' WHERE s.xtype = ''u'' AND s.name = ''rp_DBProperties'' ) ' +
' INSERT INTO #isexist(isexist) VALUES(1) '
PRINT @sql
EXEC(@sql)
IF EXISTS(SELECT * FROM #isexist)
BEGIN
SET @sql = ' INSERT INTO #gsysobjects (dbname, dbid, id, name, namedb) SELECT ' +
''''+ @dbname + '''' + ',' + CAST(@dbid as varchar)+', id, name, DBName FROM [' +
@dbname+'].dbo.sysobjects, ['+ @dbname +
'].dbo.rp_DBProperties WHERE xtype = ''u'' AND name = ''rp_DBProperties'''
PRINT @sql
EXEC (@sql)
END
FETCH NEXT FROM curDB INTO @dbname, @dbid
END
CLOSE curDB
DEALLOCATE curDB
SELECT dbname + CASE WHEN namedb is null then '' else ' ' + namedb END DBS, *
FROM #gsysobjects
order by dbname
DROP TABLE #gsysobjects
DROP TABLE #isexist
SET NOCOUNT OFF
";
List <OSBBDatabase> result = new List <OSBBDatabase>();
SqlCommand command = new SqlCommand(SELECT_OSBB_DBS_QUERY, connection);
command.CommandType = CommandType.Text;
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
OSBBDatabase db = new OSBBDatabase()
{
Alias = (string)reader["DBS"],
DatabaseID = (int)reader["dbname"],
DBName = (string)reader["DbID"]
};
result.Add(db);
}
return(result);
}
