Home / Questions / 64287
Accepted
SeWaNsX
Asked: 2020-04-21 10:55:26 +0800 CST 2020-04-21 10:55:26 +0800 CST

List all databases on a server in SQL Server 2008

  • 772

I'm trying to get a small report from SQL Server 2008.

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

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

He sp_helpdbgives me certain data and I can't match 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 idea how to do it?

sql

2 Answers

  1. Best Answer

    Hoping you 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;
    • 4 
  2. SELECT *FROM sys.databases 
SELECT * FROM [SYS].[COLUMNS] --Mostrar en columnas
SELECT * FROM [SYS].[TABLES] --Mostrar en tablas

--NOMBRE DE LAS BASES DE DATOS Y LA FECHA DE CREACIÓN-----

SELECT NAME AS [NOMBRE], 
CONVERT( VARCHAR(10),CRDATE,105) AS [FECH CREACIÓN]
FROM SYSDATABASES
ORDER BY CRDATE
    • 0