Служебные запросы на сервере MS SQL

Служебные запросы на сервере MS SQL

Версия Базы

SELECT      SUBSTRING(@@VERSION, 22, 4) AS Version, @@VERSION AS FullVersion, DB_NAME() AS Base, USER_NAME() AS Login

Все Объекты

SELECT      name, object_id, principal_id, schema_id, parent_object_id, type, type_desc, create_date, modify_date, is_ms_shipped, is_published,
                        is_schema_published
FROM          sys.objects

Запросы с параметрами

SELECT      TOP 100 PERCENT SCHEMA_NAME(SCHEMA_ID) AS Schem, SO.name AS ObjectName, SO.Type_Desc AS [ObjectType (UDF/SP)],
                        P.parameter_id AS ParameterID, P.name AS ParameterName, TYPE_NAME(P.user_type_id) AS ParameterDataType,
                        P.max_length AS ParameterMaxBytes, P.is_output AS IsOutPutParameter, SO.OBJECT_ID
FROM          sys.objects SO INNER JOIN
                        sys.parameters P ON SO.OBJECT_ID = P.OBJECT_ID
WHERE      (SO.OBJECT_ID IN
                            (SELECT      OBJECT_ID
                              FROM           sys.objects
                              WHERE       TYPE IN ('P', 'FN', 'IF'))) AND (NOT (P.name IS NULL))
ORDER BY Schem, SO.name, P.parameter_id

Расширенные - Подстановки

SELECT      OBJECT_NAME(ob.object_id) AS TableName, ex.name AS Prop, ex.value AS Extended, type, SCHEMA_NAME(SCHEMA_ID) AS Schem
FROM          sys.objects ob INNER JOIN
                        sys.extended_properties ex ON ob.object_id = ex.major_id
WHERE      (OBJECT_NAME(ob.object_id) LIKE N'Детали') AND (ex.name LIKE N'MS_SubdatasheetName' OR
                        ex.name LIKE N'MS_Link%')

Расширенные Свойства Объектов

SELECT      TOP 100 PERCENT tab2.minor_id, tab2.TableName, tab2.ColumnName, tab1.RowSource, tab2.MSColumnWidth
FROM          (SELECT      TOP 100 PERCENT ex.minor_id, OBJECT_NAME(c.object_id) AS [TableNam], c.name AS [ColumnNam], ex.value AS RowSource
                        FROM           sys.columns c LEFT OUTER JOIN
                                                sys.extended_properties ex ON ex.major_id = c.object_id AND ex.minor_id = c.column_id AND ex.name = 'MS_RowSource'
                        WHERE       (OBJECTPROPERTY(c.object_id, 'IsMsShipped') = 0)
                        ORDER BY OBJECT_NAME(c.object_id)) tab1 FULL OUTER JOIN
                            (SELECT      TOP 100 PERCENT ex.minor_id, OBJECT_NAME(c.object_id) AS [TableName], c.name AS [ColumnName],
                                                      ex.value AS MSColumnWidth
                              FROM           sys.columns c LEFT OUTER JOIN
                                                      sys.extended_properties ex ON ex.major_id = c.object_id AND ex.minor_id = c.column_id AND ex.name = 'MS_ColumnWidth'
                              WHERE       (OBJECTPROPERTY(c.object_id, 'IsMsShipped') = 0)
                              ORDER BY OBJECT_NAME(c.object_id)) tab2 ON tab1.ColumnNam = tab2.ColumnName AND tab1.TableNam = tab2.TableName
WHERE      (tab2.TableName = N'Проекты')
ORDER BY tab1.minor_id

или

SELECT      OBJECT_NAME(c.object_id) AS TableName, c.name AS ColumnName, major_id, minor_id, ex.name, ex.value AS Extended, type, type_desc,
                        SCHEMA_NAME(SCHEMA_ID) AS Schem
FROM          sys.objects ob FULL OUTER JOIN
                        sys.columns c ON ob.object_id = c.object_id FULL OUTER JOIN
                        sys.extended_properties ex ON ex.minor_id = c.column_id AND ex.major_id = c.object_id
WHERE      (OBJECT_NAME(c.object_id) = N'Проекты') AND (OBJECTPROPERTY(c.object_id, 'IsMsShipped') = 0)

или

SELECT      TOP 100 PERCENT ex.minor_id, OBJECT_NAME(c.object_id) AS TableN, c.name AS ColumnN, ex.value AS ColumnWidth
FROM          sys.columns c LEFT OUTER JOIN
                        sys.extended_properties ex ON ex.major_id = c.object_id AND ex.minor_id = c.column_id AND ex.name = 'ColumnWidth'
WHERE      (OBJECTPROPERTY(c.object_id, 'IsMsShipped') = 0) AND (OBJECT_NAME(c.object_id) = N'Детали')
ORDER BY OBJECT_NAME(c.object_id)

Расширенные Свойства - Сортировка

SELECT DISTINCT name, value
FROM          sys.extended_properties
WHERE      (name LIKE 'MS_OrderBy')

Роли Запросов

SELECT      OBJECT_NAME(id) AS Объект, USER_NAME(uid) AS Роль, action
FROM          sys.sysprotects
WHERE      (USER_NAME(uid) <> 'public')

 Инфа о пользователях и их роли

SELECT      CASE sp.type WHEN 'u' THEN 'WIN' WHEN 's' THEN 'SQL' WHEN 'g' THEN 'GRP' END AS [Login Type], CONVERT(char(45), sp.name) AS srvLogin,
                        CONVERT(char(45), sp2.name) AS srvRole, CONVERT(char(25), dbp.name) AS dbUser, CONVERT(char(25), dbp2.name) AS dbRole,
                        sp.create_date AS USER_create_date, dbo.Пользователи.Логин, dbo.Пользователи.ФИО, dbo.Пользователи.Подразделение
FROM          sys.server_principals sp INNER JOIN
                        sys.database_principals dbp ON sp.sid = dbp.sid INNER JOIN
                        sys.database_role_members dbrm ON dbp.principal_Id = dbrm.member_principal_Id INNER JOIN
                        sys.database_principals dbp2 ON dbrm.role_principal_id = dbp2.principal_id LEFT OUTER JOIN
                        sys.server_role_members srm ON sp.principal_id = srm.member_principal_id LEFT OUTER JOIN
                        sys.server_principals sp2 ON srm.role_principal_id = sp2.principal_id LEFT OUTER JOIN
                        dbo.Пользователи ON CONVERT(char(25), dbp.name) = dbo.Пользователи.Логин

  Активные пользователи

SELECT      TOP 100 PERCENT master.sys.sysprocesses.loginame AS логин, dbo.Пользователи.ФИО,
                        dbo.Пользователи.Отдел, COUNT(master.sys.sysprocesses.loginame) AS соединения, SUM(master.sys.sysprocesses.physical_io)
                        AS операций
FROM          dbo.Подразделения RIGHT OUTER JOIN
                        dbo.Пользователи ON dbo.Подразделения.Код = dbo.Пользователи.Подразделение RIGHT OUTER JOIN
                        master.sys.sysprocesses ON dbo.Пользователи.Логин = master.sys.sysprocesses.loginame
WHERE      (master.sys.sysprocesses.dbid = DB_ID('База'))
GROUP BY master.sys.sysprocesses.loginame, dbo.Пользователи.ФИО, dbo.Пользователи.Отдел
ORDER BY master.sys.sysprocesses.loginame

 Получение списка владельцев базы

ALTER PROCEDURE DbOwnersФормирование
AS  SET NOCOUNT ON

IF OBJECT_ID('tempdb..##Temp') IS NOT NULL BEGIN DROP TABLE ##Temp END
IF OBJECT_ID('tempdb..##Temp') IS NULL
begin
CREATE  TABLE  ##Temp (
    DBName SYSNAME,
    Username SYSNAME,
    Rolename NVARCHAR(50),
    IsOwner BIT
)
end

    '
        USE [База];
        INSERT INTO ##Temp
            SELECT
                DB_NAME(),
                dp.name,
                dp2.name,
                CASE
                    WHEN dp2.name = ''db_owner'' THEN 1
                    ELSE 0
                END AS ''db_owner''
            FROM
                sys.database_principals AS dp
            LEFT JOIN
                sys.database_role_members AS rm
                    ON
                        rm.member_principal_id = dp.principal_id
            LEFT JOIN
                sys.database_principals AS dp2
                    ON
                        rm.role_principal_id = dp2.principal_id
            WHERE
                dp.type <> ''R''
    '
RETURN 

EXEC sp_MSforeachdb

и потом :

ALTER PROCEDURE dbo.DbOwnersПолучение
AS SELECT      *
FROM          dbo.[##Temp]
RETURN

Удаление роли пользователя

ALTER PROCEDURE dbo.УдалениеРолиПользователя
(
 @уКого nvarchar(50),
 @роль varchar(255) )
AS
/* SET NOCOUNT ON */
-- ALTER ROLE  @роль DROP MEMBER @уКого;   //2012 SQL
EXEC sp_droprolemember  @роль, @уКого;

Добавление роли пользователю

ALTER PROCEDURE dbo.ДобавлениеРолиПользователю
(
 @Кому nvarchar(50),
 @роль varchar(255) )
AS
/* SET NOCOUNT ON */
-- ALTER ROLE  @роль ADD MEMBER @Кому; //2012 SQL
EXEC sp_addrolemember @роль, @Кому;

Предоставить доступ к объекту

ALTER PROCEDURE ДатьДоступКобъекту
(
@объект nvarchar(50),   -- входной параметр, идентификатор
@КомуДоступ varchar(255) )
AS
SET NOCOUNT ON
BEGIN
  DECLARE @s nvarchar(1024)
  SET @s=''
SET @s= 'GRANT SELECT ON   @объект  TO @КомуДоступ    '
   EXEC (@s)
END

Изменения за последние 10 дней

SELECT      TOP 100 PERCENT name AS object_name, SCHEMA_NAME(schema_id) AS schema_name, type_desc, create_date, modify_date
FROM          sys.objects
WHERE      (modify_date > GETDATE() - 10)
ORDER BY modify_date

Получить Объем Базы Данных

ALTER PROCEDURE [схема].ОбъемБазыДанных
AS
SET NOCOUNT ON
-- DBCC UPDATEUSAGE(0)
-- DB size.
EXEC sp_spaceused
-- Table row counts and sizes.
CREATE TABLE #t
(
    [name] NVARCHAR(128),
    [rows] CHAR(11),
    reserved VARCHAR(18),
    data VARCHAR(18),
    index_size VARCHAR(18),
    unused VARCHAR(18)
)
INSERT #t EXEC sp_msForEachTable 'EXEC sp_spaceused ''?'''
SELECT *
FROM   #t
-- # of rows.
SELECT SUM(CAST([rows] AS int)) AS [rows]
FROM   #t
DROP TABLE #t

Список Ключей-Связей Столбцов

SELECT      TOP (100) PERCENT LEFT(QUOTENAME(FK.CONSTRAINT_NAME), 50) AS [Foreign Key], CAST(QUOTENAME(FK.TABLE_CATALOG)
                        + '.' + QUOTENAME(FK.TABLE_SCHEMA) + '.' + QUOTENAME(FK.TABLE_NAME) + '.' + QUOTENAME(FK.COLUMN_NAME) AS NVARCHAR(530))
                        AS [поле, которое ссылается], CAST(QUOTENAME(PK.TABLE_CATALOG) + '.' + QUOTENAME(PK.TABLE_SCHEMA)
                        + '.' + QUOTENAME(PK.TABLE_NAME) + '.' + QUOTENAME(PK.COLUMN_NAME) AS NVARCHAR(530)) AS [поле, на которое ссылается]
FROM          INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS PK INNER JOIN
                        INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS C ON PK.CONSTRAINT_CATALOG = C.UNIQUE_CONSTRAINT_CATALOG AND
                        PK.CONSTRAINT_SCHEMA = C.UNIQUE_CONSTRAINT_SCHEMA AND PK.CONSTRAINT_NAME = C.UNIQUE_CONSTRAINT_NAME INNER JOIN
                        INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS FK ON C.CONSTRAINT_CATALOG = PK.CONSTRAINT_CATALOG AND
                        C.CONSTRAINT_SCHEMA = FK.CONSTRAINT_SCHEMA AND C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME AND
                        PK.ORDINAL_POSITION = FK.ORDINAL_POSITION
ORDER BY FK.CONSTRAINT_NAME, PK.ORDINAL_POSITION

 Связи Таблицы

SELECT      TOP 100 PERCENT FK.COLUMN_NAME AS Field, PK.COLUMN_NAME AS Source, FK.TABLE_NAME AS FieldTab, PK.TABLE_NAME AS SourceTab
FROM          INFORMATION_SCHEMA.KEY_COLUMN_USAGE PK INNER JOIN
                        INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C ON PK.CONSTRAINT_CATALOG = C.UNIQUE_CONSTRAINT_CATALOG AND
                        PK.CONSTRAINT_SCHEMA = C.UNIQUE_CONSTRAINT_SCHEMA AND PK.CONSTRAINT_NAME = C.UNIQUE_CONSTRAINT_NAME INNER JOIN
                        INFORMATION_SCHEMA.KEY_COLUMN_USAGE FK ON C.CONSTRAINT_CATALOG = PK.CONSTRAINT_CATALOG AND
                        C.CONSTRAINT_SCHEMA = FK.CONSTRAINT_SCHEMA AND C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME AND
                        PK.ORDINAL_POSITION = FK.ORDINAL_POSITION

Случайные 5 пользователей

SELECT      TOP 5 ROW_NUMBER() OVER (ORDER BY Отдел ASC) AS '№', ФИО, Отдел
FROM          dbo.Пользователи
WHERE      (Отдел LIKE N'Б%')
ORDER BY NEWID()