Microsoft SQL Server
Informatie over de database ophalen

.NET Framework ASP.NET C# Language Entity Framework Java Language MySQL Oracle Database PHP SQL Visual Basic .NET Language
Zoeken…


Opmerkingen

Net als bij andere relationele databasesystemen, stelt SQL Server metagegevens over uw databases bloot.

Dit wordt geleverd via het ISO-standaard INFORMATION_SCHEMA schema of de SQL Server-specifieke sys catalogusviews.

Tel het aantal tabellen in een database

Deze query retourneert het aantal tabellen in de opgegeven database. 

USE YourDatabaseName
SELECT COUNT(*) from INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

Dit is een andere manier om dit te doen voor alle gebruikerstabellen met SQL Server 2008+. De referentie is hier . 

SELECT COUNT(*) FROM sys.tables

Een lijst met alle opgeslagen procedures ophalen

De volgende query's retourneren een lijst met alle opgeslagen procedures in de database, met basisinformatie over elke opgeslagen procedure:

SQL Server 2005 
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE'

De ROUTINE_NAME , ROUTINE_SCHEMA en ROUTINE_DEFINITION zijn over het algemeen het handigst.

SQL Server 2005 
SELECT *
FROM sys.objects
WHERE type = 'P'
SQL Server 2005 
SELECT *
FROM sys.procedures

Merk op dat deze versie een voordeel heeft boven het selecteren uit sys.objects, omdat het de extra kolommen bevat is_auto_executed , is_execution_replicated , is_repl_serializable en skips_repl_constraints .

SQL Server 2005 
SELECT *
FROM sysobjects
WHERE type = 'P'

Merk op dat de uitvoer veel kolommen bevat die nooit betrekking hebben op een opgeslagen procedure.

De volgende reeks zoekopdrachten retourneert alle opgeslagen procedures in de database die de tekenreeks 'SearchTerm' bevatten:

SQL Server 2005 
SELECT o.name
FROM syscomments c
INNER JOIN sysobjects o
    ON c.id=o.id
WHERE o.xtype = 'P'
    AND c.TEXT LIKE '%SearchTerm%'
SQL Server 2005 
SELECT p.name
FROM sys.sql_modules AS m
INNER JOIN sys.procedures AS p
    ON m.object_id = p.object_id
WHERE definition LIKE '%SearchTerm%'

Download de lijst met alle databases op een server

Methode 1: De onderstaande zoekopdracht is van toepassing op de versie van SQL Server 2000+ (bevat 12 kolommen) 

SELECT * FROM dbo.sysdatabases

Methode 2: Hieronder query-extractinformatie over databases met meer informatie (bijvoorbeeld: status, isolatie, herstelmodel, enz.)

Opmerking: dit is een catalogusweergave en zal beschikbaar zijn in SQL SERVER 2005+ versies 

SELECT * FROM sys.databases

Methode 3: Om alleen databasenamen te zien, kunt u sp_MSForEachDB zonder papieren gebruiken 

EXEC sp_MSForEachDB 'SELECT ''?'' AS DatabaseName'

Methode 4: Hieronder SP helpt u om de databasegrootte samen met de databasenaam, eigenaar, status etc. op de server aan te bieden 

EXEC sp_helpdb

Methode 5 Op dezelfde manier geeft de onderstaande opgeslagen procedure de naam van de database, de grootte van de database en opmerkingen 

EXEC sp_databases

Database bestanden

Toon alle gegevensbestanden voor alle databases met informatie over grootte en groei 

SELECT  d.name AS 'Database',
        d.database_id,  
        SF.fileid,
        SF.name AS 'LogicalFileName',   
        CASE SF.status & 0x100000  
            WHEN 1048576 THEN 'Percentage' 
            WHEN 0 THEN 'MB' 
        END AS 'FileGrowthOption',
        Growth AS GrowthUnit,
        ROUND(((CAST(Size AS FLOAT)*8)/1024)/1024,2) [SizeGB], -- Convert 8k pages to GB
        Maxsize,        
        filename AS PhysicalFileName

FROM    Master.SYS.SYSALTFILES SF
Join    Master.SYS.Databases d on sf.fileid = d.database_id

Order by d.name

Database-opties ophalen

De volgende query retourneert de database-opties en metagegevens: 

select * from sys.databases WHERE name = 'MyDatabaseName';

Grootte van alle tabellen in huidige database weergeven

SELECT
    s.name + '.' + t.NAME AS TableName,
    SUM(a.used_pages)*8 AS 'TableSizeKB'  --a page in SQL Server is 8kb
FROM sys.tables t
    JOIN sys.schemas s on t.schema_id = s.schema_id
    LEFT JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
    LEFT JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
    LEFT JOIN sys.allocation_units a ON p.partition_id = a.container_id
GROUP BY
    s.name, t.name
ORDER BY
    --Either sort by name:
    s.name + '.' + t.NAME
    --Or sort largest to smallest:
    --SUM(a.used_pages) desc

Bepaal het permissiepad van een Windows Login

Dit toont het gebruikerstype en het machtigingspad (van welke Windows-groep de gebruiker zijn machtigingen krijgt). 

xp_logininfo 'DOMAIN\user'

Tabellen ophalen met bekende kolom

Deze zoekopdracht retourneert alle COLUMNS en de bijbehorende TABLES voor een bepaalde kolomnaam. Het is ontworpen om u te laten zien welke tabellen (onbekend) een opgegeven kolom bevatten (bekend) 

SELECT 
    c.name AS ColName, 
    t.name AS TableName
FROM 
    sys.columns c
    JOIN sys.tables t ON c.object_id = t.object_id
WHERE 
    c.name LIKE '%MyName%'

Kijk of er Enterprise-specifieke functies worden gebruikt

Het is soms handig om te controleren of uw werk aan de ontwikkelaarsversie geen afhankelijkheid heeft geïntroduceerd van functies die beperkt zijn tot de Enterprise-editie.

U kunt dit doen met behulp van de sys.dm_db_persisted_sku_features , zoals hier: 

SELECT * FROM sys.dm_db_persisted_sku_features

Tegen de database zelf.

Hier worden de functies weergegeven die worden gebruikt, indien aanwezig.

Zoek en retourneer alle tabellen en kolommen die een gespecificeerde kolomwaarde bevatten

Dit script retourneert hier en hier alle tabellen en kolommen waarvoor een opgegeven waarde bestaat. Dit is krachtig om uit te zoeken waar een bepaalde waarde in een database zit. Het kan belastend zijn, dus er wordt gesuggereerd dat het eerst in een back-up / testomgeving wordt uitgevoerd. 

DECLARE @SearchStr nvarchar(100)
SET @SearchStr = '## YOUR STRING HERE ##'
 
 
    -- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
    -- Purpose: To search all columns of all tables for a given search string
    -- Written by: Narayana Vyas Kondreddi
    -- Site: http://vyaskn.tripod.com
    -- Updated and tested by Tim Gaunt
    -- http://www.thesitedoctor.co.uk
    -- http://blogs.thesitedoctor.co.uk/tim/2010/02/19/Search+Every+Table+And+Field+In+A+SQL+Server+Database+Updated.aspx
    -- Tested on: SQL Server 7.0, SQL Server 2000, SQL Server 2005 and SQL Server 2010
    -- Date modified: 03rd March 2011 19:00 GMT
    CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
 
    SET NOCOUNT ON
 
    DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
    SET  @TableName = ''
    SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
 
    WHILE @TableName IS NOT NULL
     
    BEGIN
        SET @ColumnName = ''
        SET @TableName =
        (
            SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
            FROM     INFORMATION_SCHEMA.TABLES
            WHERE         TABLE_TYPE = 'BASE TABLE'
                AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
                AND    OBJECTPROPERTY(
                        OBJECT_ID(
                            QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                             ), 'IsMSShipped'
                               ) = 0
        )
 
        WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
             
        BEGIN
            SET @ColumnName =
            (
                SELECT MIN(QUOTENAME(COLUMN_NAME))
                FROM     INFORMATION_SCHEMA.COLUMNS
                WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                    AND    TABLE_NAME    = PARSENAME(@TableName, 1)
                    AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')
                    AND    QUOTENAME(COLUMN_NAME) > @ColumnName
            )
     
            IF @ColumnName IS NOT NULL
             
            BEGIN
                INSERT INTO #Results
                EXEC
                (
                    'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' +
                    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
                )
            END
        END   
    END
 
    SELECT ColumnName, ColumnValue FROM #Results
 
DROP TABLE #Results
- See more at: http://thesitedoctor.co.uk/blog/search-every-table-and-field-in-a-sql-server-database-updated#sthash.bBEqfJVZ.dpuf

Download alle schema's, tabellen, kolommen en indexen

SELECT 
    s.name AS [schema],
    t.object_id AS [table_object_id], 
    t.name AS [table_name],
    c.column_id,
    c.name AS [column_name],
    i.name AS [index_name],
    i.type_desc AS [index_type]
FROM sys.schemas AS s
INNER JOIN sys.tables AS t 
    ON s.schema_id = t.schema_id
INNER JOIN sys.columns AS c 
    ON t.object_id = c.object_id
LEFT JOIN sys.index_columns AS ic 
    ON c.object_id = ic.object_id and c.column_id = ic.column_id
LEFT JOIN sys.indexes AS i 
    ON ic.object_id = i.object_id and ic.index_id = i.index_id
ORDER BY [schema], [table_name], c.column_id;

Retourneer een lijst met SQL Agent-taken, met planningsinformatie

USE msdb
Go


SELECT dbo.sysjobs.Name AS 'Job Name', 
      'Job Enabled' = CASE dbo.sysjobs.Enabled
            WHEN 1 THEN 'Yes'
            WHEN 0 THEN 'No'
      END,
      'Frequency' = CASE dbo.sysschedules.freq_type
            WHEN 1 THEN 'Once'
            WHEN 4 THEN 'Daily'
            WHEN 8 THEN 'Weekly'
            WHEN 16 THEN 'Monthly'
            WHEN 32 THEN 'Monthly relative'
            WHEN 64 THEN 'When SQLServer Agent starts'
      END, 
      'Start Date' = CASE active_start_date
            WHEN 0 THEN null
            ELSE
            substring(convert(varchar(15),active_start_date),1,4) + '/' + 
            substring(convert(varchar(15),active_start_date),5,2) + '/' + 
            substring(convert(varchar(15),active_start_date),7,2)
      END,
      'Start Time' = CASE len(active_start_time)
            WHEN 1 THEN cast('00:00:0' + right(active_start_time,2) as char(8))
            WHEN 2 THEN cast('00:00:' + right(active_start_time,2) as char(8))
            WHEN 3 THEN cast('00:0' 
                        + Left(right(active_start_time,3),1)  
                        +':' + right(active_start_time,2) as char (8))
            WHEN 4 THEN cast('00:' 
                        + Left(right(active_start_time,4),2)  
                        +':' + right(active_start_time,2) as char (8))
            WHEN 5 THEN cast('0' 
                        + Left(right(active_start_time,5),1) 
                        +':' + Left(right(active_start_time,4),2)  
                        +':' + right(active_start_time,2) as char (8))
            WHEN 6 THEN cast(Left(right(active_start_time,6),2) 
                        +':' + Left(right(active_start_time,4),2)  
                        +':' + right(active_start_time,2) as char (8))
      END,

      CASE len(run_duration)
            WHEN 1 THEN cast('00:00:0'
                        + cast(run_duration as char) as char (8))
            WHEN 2 THEN cast('00:00:'
                        + cast(run_duration as char) as char (8))
            WHEN 3 THEN cast('00:0' 
                        + Left(right(run_duration,3),1)  
                        +':' + right(run_duration,2) as char (8))
            WHEN 4 THEN cast('00:' 
                        + Left(right(run_duration,4),2)  
                        +':' + right(run_duration,2) as char (8))
            WHEN 5 THEN cast('0' 
                        + Left(right(run_duration,5),1) 
                        +':' + Left(right(run_duration,4),2)  
                        +':' + right(run_duration,2) as char (8))
            WHEN 6 THEN cast(Left(right(run_duration,6),2) 
                        +':' + Left(right(run_duration,4),2)  
                        +':' + right(run_duration,2) as char (8))
      END as 'Max Duration',
    CASE(dbo.sysschedules.freq_subday_interval)
            WHEN 0 THEN 'Once'
            ELSE cast('Every ' 
                        + right(dbo.sysschedules.freq_subday_interval,2) 
                        + ' '
                        +     CASE(dbo.sysschedules.freq_subday_type)
                                          WHEN 1 THEN 'Once'
                                          WHEN 4 THEN 'Minutes'
                                          WHEN 8 THEN 'Hours'
                                    END as char(16))
    END as 'Subday Frequency'
FROM dbo.sysjobs 
LEFT OUTER JOIN dbo.sysjobschedules 
ON dbo.sysjobs.job_id = dbo.sysjobschedules.job_id
INNER JOIN dbo.sysschedules ON dbo.sysjobschedules.schedule_id = dbo.sysschedules.schedule_id 
LEFT OUTER JOIN (SELECT job_id, max(run_duration) AS run_duration
            FROM dbo.sysjobhistory
            GROUP BY job_id) Q1
ON dbo.sysjobs.job_id = Q1.job_id
WHERE Next_run_time = 0

UNION

SELECT dbo.sysjobs.Name AS 'Job Name', 
      'Job Enabled' = CASE dbo.sysjobs.Enabled
            WHEN 1 THEN 'Yes'
            WHEN 0 THEN 'No'
      END,
      'Frequency' = CASE dbo.sysschedules.freq_type
            WHEN 1 THEN 'Once'
            WHEN 4 THEN 'Daily'
            WHEN 8 THEN 'Weekly'
            WHEN 16 THEN 'Monthly'
            WHEN 32 THEN 'Monthly relative'
            WHEN 64 THEN 'When SQLServer Agent starts'
      END, 
      'Start Date' = CASE next_run_date
            WHEN 0 THEN null
            ELSE
            substring(convert(varchar(15),next_run_date),1,4) + '/' + 
            substring(convert(varchar(15),next_run_date),5,2) + '/' + 
            substring(convert(varchar(15),next_run_date),7,2)
      END,
      'Start Time' = CASE len(next_run_time)
            WHEN 1 THEN cast('00:00:0' + right(next_run_time,2) as char(8))
            WHEN 2 THEN cast('00:00:' + right(next_run_time,2) as char(8))
            WHEN 3 THEN cast('00:0' 
                        + Left(right(next_run_time,3),1)  
                        +':' + right(next_run_time,2) as char (8))
            WHEN 4 THEN cast('00:' 
                        + Left(right(next_run_time,4),2)  
                        +':' + right(next_run_time,2) as char (8))
            WHEN 5 THEN cast('0' + Left(right(next_run_time,5),1) 
                        +':' + Left(right(next_run_time,4),2)  
                        +':' + right(next_run_time,2) as char (8))
            WHEN 6 THEN cast(Left(right(next_run_time,6),2) 
                        +':' + Left(right(next_run_time,4),2)  
                        +':' + right(next_run_time,2) as char (8))
      END,

      CASE len(run_duration)
            WHEN 1 THEN cast('00:00:0'
                        + cast(run_duration as char) as char (8))
            WHEN 2 THEN cast('00:00:'
                        + cast(run_duration as char) as char (8))
            WHEN 3 THEN cast('00:0' 
                        + Left(right(run_duration,3),1)  
                        +':' + right(run_duration,2) as char (8))
            WHEN 4 THEN cast('00:' 
                        + Left(right(run_duration,4),2)  
                        +':' + right(run_duration,2) as char (8))
            WHEN 5 THEN cast('0' 
                        + Left(right(run_duration,5),1) 
                        +':' + Left(right(run_duration,4),2)  
                        +':' + right(run_duration,2) as char (8))
            WHEN 6 THEN cast(Left(right(run_duration,6),2) 
                        +':' + Left(right(run_duration,4),2)  
                        +':' + right(run_duration,2) as char (8))
      END as 'Max Duration',
    CASE(dbo.sysschedules.freq_subday_interval)
            WHEN 0 THEN 'Once'
            ELSE cast('Every ' 
                        + right(dbo.sysschedules.freq_subday_interval,2) 
                        + ' '
                        +     CASE(dbo.sysschedules.freq_subday_type)
                                          WHEN 1 THEN 'Once'
                                          WHEN 4 THEN 'Minutes'
                                          WHEN 8 THEN 'Hours'
                                    END as char(16))
    END as 'Subday Frequency'
FROM dbo.sysjobs 
LEFT OUTER JOIN dbo.sysjobschedules ON dbo.sysjobs.job_id = dbo.sysjobschedules.job_id
INNER JOIN dbo.sysschedules ON dbo.sysjobschedules.schedule_id = dbo.sysschedules.schedule_id 
LEFT OUTER JOIN (SELECT job_id, max(run_duration) AS run_duration
            FROM dbo.sysjobhistory
            GROUP BY job_id) Q1
ON dbo.sysjobs.job_id = Q1.job_id
WHERE Next_run_time <> 0

ORDER BY [Start Date],[Start Time]

Informatie ophalen over back-up- en herstelbewerkingen

Ga als volgt te werk om de lijst met alle back-upbewerkingen op het huidige database-exemplaar te krijgen: 

SELECT sdb.Name AS DatabaseName,
    COALESCE(CONVERT(VARCHAR(50), bus.backup_finish_date, 120),'-') AS LastBackUpDateTime
FROM sys.sysdatabases sdb
    LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name
ORDER BY sdb.name, bus.backup_finish_date DESC

Om de lijst met alle herstelbewerkingen op het huidige database-exemplaar te krijgen: 

SELECT 
    [d].[name] AS database_name, 
    [r].restore_date AS last_restore_date, 
    [r].[user_name], 
    [bs].[backup_finish_date] AS backup_creation_date, 
    [bmf].[physical_device_name] AS [backup_file_used_for_restore] 
FROM master.sys.databases [d] 
    LEFT OUTER JOIN msdb.dbo.[restorehistory] r ON r.[destination_database_name] = d.Name 
    INNER JOIN msdb.dbo.backupset [bs] ON [r].[backup_set_id] = [bs].[backup_set_id] 
    INNER JOIN msdb.dbo.backupmediafamily bmf ON [bs].[media_set_id] = [bmf].[media_set_id] 
ORDER BY [d].[name], [r].restore_date DESC

Vind elke vermelding van een veld in de database

SELECT DISTINCT
 o.name AS Object_Name,o.type_desc
 FROM sys.sql_modules m 
    INNER JOIN sys.objects  o ON m.object_id=o.object_id
 WHERE m.definition Like '%myField%'
 ORDER BY 2,1

Zal vermeldingen van myField in SProcs, Views, etc.



Modified text is an extract of the original Stack Overflow Documentation
Licentie onder CC BY-SA 3.0
Niet aangesloten bij Stack Overflow