List all the databases of a server in SQL Server 2008

Question

List all the databases of a server in SQL Server 2008

Navigation

#1 by (3 votes)

4

I try to get a small report of SQL Server 2008.

I need to list all the databases created on the server, and include:

Name
creation date
user (the one with db_owner permissions)
and size

The sp_helpdb gives me certain information and I can not link it with my query:

SET language 'Spanish'
SELECT 
    name AS [Nombre DB]
    ,LEFT(DATENAME(WEEKDAY,crdate),10) + ' ' + CONVERT(VARCHAR(10),crdate,105) AS [Fecha Creación] 
FROM master.dbo.sysdatabases
order by crdate

Any ideas on how to do it?

sql sql-server-2008

asked by Esteban Jaramillo 20.04.2017 в 20:55

source

1 answer

Sorting array or vectors in c # [closed] Consult MySQL with OR and AND clauses?

score 3 · Accepted Answer

Waiting for you to have the necessary permissions, use:

DECLARE @databaseName sysname
   ,@databaseDate VARCHAR(50)
   ,@databaseSize NUMERIC(9, 3)
   ,@SQL VARCHAR(MAX);

DECLARE @CONSOLIDADO TABLE
(
    [Nombre] VARCHAR(100)
   ,[Usuario] VARCHAR(100)
   ,[Fecha Creacion] VARCHAR(50)
   ,[Tamaño MB] NUMERIC(9, 3)
);

DECLARE databaseCursor CURSOR
FOR
SELECT name
   ,LEFT(DATENAME(WEEKDAY, create_date), 10) + ' '
    + CONVERT(VARCHAR(10), create_date, 105)
   ,(
        SELECT SUM((size * 8) / 1024.00)
        FROM sys.master_files
        WHERE database_id = db.database_id
    )
FROM sys.databases db;

OPEN databaseCursor;
FETCH NEXT FROM databaseCursor
INTO @databaseName
   ,@databaseDate
   ,@databaseSize;

WHILE (
          @@FETCH_STATUS = 0
      )
BEGIN
    SET @SQL
        = N'SELECT ''' + @databaseName + '''
          ,p.name
          ,''' + @databaseDate + '''
          ,''' + CONVERT(VARCHAR(9), @databaseSize) + '''
        FROM ' + @databaseName
          + '.sys.database_role_members m
        INNER JOIN ' + @databaseName
          + '.sys.database_principals p on m.member_principal_id = p.principal_id 
        WHERE 
            USER_NAME(role_principal_id) IN (''db_owner'')
            AND p.name NOT IN (''dbo'')';

    INSERT INTO @CONSOLIDADO
    EXEC(@SQL);
    FETCH NEXT FROM databaseCursor
    INTO @databaseName
       ,@databaseDate
       ,@databaseSize;

END;
CLOSE databaseCursor;
DEALLOCATE databaseCursor;

SELECT *
FROM @CONSOLIDADO;