Microsoft SQL Server
Pobierz informacje o bazie danych

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


Uwagi

Podobnie jak w przypadku innych systemów relacyjnych baz danych, SQL Server udostępnia metadane dotyczące twoich baz danych.

Jest to zapewniane za pomocą schematu ISO INFORMATION_SCHEMA standardzie ISO lub specyficznych dla SQL Server widoków katalogu sys .

Policz liczbę tabel w bazie danych

To zapytanie zwróci liczbę tabel w określonej bazie danych. 

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

Poniżej przedstawiono inny sposób, w jaki można to zrobić dla wszystkich tabel użytkowników w SQL Server 2008+. Referencje są tutaj . 

SELECT COUNT(*) FROM sys.tables

Pobierz listę wszystkich przechowywanych procedur

Poniższe zapytania zwrócą listę wszystkich procedur przechowywanych w bazie danych wraz z podstawowymi informacjami o każdej procedurze przechowywanej:

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

ROUTINE_NAME , ROUTINE_SCHEMA i ROUTINE_DEFINITION są na ogół najbardziej przydatne.

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

Zauważ, że ta wersja ma przewagę nad wyborem z sys.objects, ponieważ zawiera dodatkowe kolumny is_auto_executed , is_execution_replicated , is_repl_serializable oraz skips_repl_constraints .

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

Zauważ, że dane wyjściowe zawierają wiele kolumn, które nigdy nie będą dotyczyły procedury składowanej.

Następny zestaw zapytań zwróci wszystkie Procedury składowane w bazie danych, które zawierają ciąg „SearchTerm”:

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%'

Uzyskaj listę wszystkich baz danych na serwerze

Metoda 1: Poniższe zapytanie będzie dotyczyć wersji SQL Server 2000+ (zawiera 12 kolumn) 

SELECT * FROM dbo.sysdatabases

Metoda 2: Poniżej zapytania wyodrębnij informacje o bazach danych z dodatkowymi informacjami (np. Stan, izolacja, model odzyskiwania itp.)

Uwaga: To jest widok katalogu i będą dostępne wersje SQL SERVER 2005+ 

SELECT * FROM sys.databases

Metoda 3: Aby wyświetlić tylko nazwy baz danych, możesz użyć nieudokumentowanego sp_MSForEachDB 

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

Metoda 4: Poniżej SP pomoże ci podać rozmiar bazy danych wraz z nazwą bazy danych, właścicielem, statusem itp. Na serwerze 

EXEC sp_helpdb

Metoda 5 Podobnie, poniżej procedura składowana poda nazwę bazy danych, rozmiar bazy danych i uwagi 

EXEC sp_databases

Pliki bazy danych

Wyświetl wszystkie pliki danych dla wszystkich baz danych z informacjami o rozmiarze i wzroście 

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

Pobierz opcje bazy danych

Następujące zapytanie zwraca opcje bazy danych i metadane: 

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

Pokaż rozmiar wszystkich tabel w bieżącej bazie danych

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

Określ ścieżkę uprawnień logowania do systemu Windows

Spowoduje to wyświetlenie typu użytkownika i ścieżki uprawnień (z której grupy okien użytkownik otrzymuje uprawnienia). 

xp_logininfo 'DOMAIN\user'

Pobierz tabele zawierające znaną kolumnę

To zapytanie zwróci wszystkie COLUMNS i powiązane z nimi TABLES dla danej nazwy kolumny. Ma on na celu pokazanie, które tabele (nieznane) zawierają określoną kolumnę (znane) 

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%'

Sprawdź, czy używane są funkcje specyficzne dla przedsiębiorstwa

Czasami przydatne jest sprawdzenie, czy praca nad wersją dla programistów nie spowodowała zależności od funkcji ograniczonych do wersji dla przedsiębiorstw.

Możesz to zrobić, korzystając z widoku systemu sys.dm_db_persisted_sku_features , w następujący sposób: 

SELECT * FROM sys.dm_db_persisted_sku_features

Przeciw samej bazie danych.

Spowoduje to wyświetlenie listy używanych funkcji, jeśli takie istnieją.

Wyszukaj i zwróć wszystkie tabele i kolumny zawierające określoną wartość kolumny

Ten skrypt, tu i tutaj , zwróci wszystkie tabele i kolumny, w których istnieje określona wartość. Jest to potężne narzędzie do znajdowania pewnej wartości w bazie danych. Może to być opodatkowanie, dlatego sugeruje się, aby najpierw wykonać w środowisku tworzenia kopii zapasowych / testowania. 

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

Uzyskaj wszystkie schematy, tabele, kolumny i indeksy

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;

Zwraca listę zadań SQL Agent z informacjami o harmonogramie

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]

Pobierz informacje o operacjach tworzenia kopii zapasowych i przywracania

Aby uzyskać listę wszystkich operacji tworzenia kopii zapasowych wykonanych w bieżącej instancji bazy danych: 

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

Aby uzyskać listę wszystkich operacji przywracania wykonanych w bieżącej instancji bazy danych: 

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

Znajdź każdą wzmiankę o polu w bazie danych

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

Znajdziesz wzmianki o myField w SProcs, Views itp.



Modified text is an extract of the original Stack Overflow Documentation
Licencjonowany na podstawie CC BY-SA 3.0
Nie związany z Stack Overflow