Microsoft SQL Server
데이터베이스에 대한 정보 검색

비고

데이터베이스의 테이블 수 계산

이 쿼리는 지정된 데이터베이스의 테이블 수를 반환합니다.

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

다음은 SQL Server 2008 이상이있는 모든 사용자 테이블에 대해 수행 할 수있는 또 다른 방법입니다. 참조는 여기에 있습니다 .

SELECT COUNT(*) FROM sys.tables

모든 저장 프로 시저 목록 검색

다음 쿼리는 각 저장 프로 시저에 대한 기본 정보와 함께 데이터베이스의 모든 저장 프로 시저 목록을 반환합니다.

SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE'

SELECT *
FROM sys.objects
WHERE type = 'P'

SELECT *
FROM sys.procedures

SELECT *
FROM sysobjects
WHERE type = 'P'

다음 쿼리 집합은 'SearchTerm'문자열을 포함하는 데이터베이스의 모든 저장 프로 시저를 반환합니다.

SELECT o.name
FROM syscomments c
INNER JOIN sysobjects o
    ON c.id=o.id
WHERE o.xtype = 'P'
    AND c.TEXT LIKE '%SearchTerm%'

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

서버의 모든 데이터베이스 목록 가져 오기

방법 1 : 아래 쿼리는 SQL Server 2000 이상 버전에 적용 가능 (12 개 열 포함)

SELECT * FROM dbo.sysdatabases

방법 2 : 쿼리 아래에서 추가 정보가있는 데이터베이스에 대한 정보를 추출합니다 (예 : 상태, 격리, 복구 모델 등).

참고 : 이것은 카탈로그 뷰이며 SQL Server 2005 이상 버전에서 사용할 수 있습니다.

SELECT * FROM sys.databases

방법 3 : 데이터베이스 이름 만 보려면 문서화되지 않은 sp_MSForEachDB를 사용할 수 있습니다.

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

방법 4 : SP 아래에서는 서버의 데이터베이스 이름, 소유자, 상태 등과 함께 데이터베이스 크기를 제공하는 데 도움이됩니다.

EXEC sp_helpdb

방법 5 마찬가지로 저장 프로 시저 아래에 데이터베이스 이름, 데이터베이스 크기 및 설명을 제공합니다

EXEC sp_databases

데이터베이스 파일

크기 및 성장 정보가있는 모든 데이터베이스의 모든 데이터 파일 표시

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

데이터베이스 옵션 검색

다음 쿼리는 데이터베이스 옵션과 메타 데이터를 반환합니다.

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

현재 데이터베이스의 모든 테이블 크기 표시

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

Windows 로그인의 사용 권한 경로 결정

그러면 사용자 유형과 권한 경로 (사용자가 권한을 얻는 Windows 그룹)가 표시됩니다.

xp_logininfo 'DOMAIN\user'

알려진 열을 포함하는 테이블 검색

이 쿼리는 모든 반환 COLUMNS 및 관련 TABLES 특정 열 이름. 이 테이블은 알 수없는 테이블에 지정된 열 (알려진)이 포함 된 테이블을 보여주기 위해 설계되었습니다.

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

엔터프라이즈 관련 기능이 사용되고 있는지 확인하십시오.

개발자 버전 작업으로 인해 Enterprise 버전으로 제한된 기능에 대한 종속성이 도입되지 않았는지 확인하는 것이 유용 할 때도 있습니다.

다음과 같이 sys.dm_db_persisted_sku_features 시스템 뷰를 사용하여이 작업을 수행 할 수 있습니다.

SELECT * FROM sys.dm_db_persisted_sku_features

데이터베이스 자체에 대한.

사용중인 기능이 있으면 목록에 표시됩니다.

지정된 열 값을 포함하는 모든 테이블과 열 검색 및 반환

이 스크립트는 여기 와 여기 에서 지정된 값이있는 모든 테이블과 열을 반환합니다. 이것은 특정 값이 데이터베이스에있는 곳을 찾는 데 효과적입니다. 세금이 부과 될 수 있으므로 먼저 백업 / 테스트 환경에서 실행하는 것이 좋습니다.

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

모든 스키마, 테이블, 컬럼 및 색인 얻기

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;

일정 정보가있는 SQL 에이전트 작업 목록 반환

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]

백업 및 복원 작업에 대한 정보 검색

현재 데이터베이스 인스턴스에서 수행 된 모든 백업 작업의 목록을 가져 오려면 다음을 수행하십시오.

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

현재 데이터베이스 인스턴스에서 수행 된 모든 복원 조작의 목록을 얻으려면 다음을 수행하십시오.

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

데이터베이스에서 필드의 모든 언급 찾기

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

SProcs, Views 등에서 myField 에 대한 언급을 찾을 수 있습니다.