Temporarily disable a trigger on a data table

Rédigé par Sozezzo - - Aucun commentaire


Disable all triggers on a table, you do what you have to do, after enable only the triggers that have been disabled.

Step 1 : Disable all triggers

--#region Disable triggers

DECLARE @triggers TABLE (SqlEnableTrigger NVARCHAR(MAX));
DECLARE @SchemaName VARCHAR(128) = 'mySchema'; – ** TO DO **
DECLARE @TableName VARCHAR(128) = 'mytable'; – ** TO DO **
DECLARE @SqlTrigger NVARCHAR(MAX);

DELETE FROM @triggers;

INSERT INTO @triggers (SqlEnableTrigger)
SELECT  'ENABLE TRIGGER '  + QUOTENAME(sc.name) + N'.' + QUOTENAME(tg.name) + N' ON ' + QUOTENAME(sc.name) + N'.' + QUOTENAME(tb.name) + N';' AS SqlEnableTrigger
FROM sys.schemas AS sc
      INNER JOIN sys.tables AS tb ON sc.schema_id = tb.schema_id
      RIGHT OUTER JOIN sys.triggers AS tg ON tb.object_id = tg.parent_id
WHERE    (sc.name =@SchemaName)
      AND (tb.name = @TableName)
      AND (tg.is_disabled = 0);

SET @SqlTrigger = 'DISABLE TRIGGER ALL ON '+QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName);
PRINT @SqlTrigger;
EXEC (@SqlTrigger);

--#endregion Disable triggers

 

Step 2: You do what you have to do.

Your code... to update, to delete, to insert, or everything you want.


Step 3 : Enable all triggers that have been disabled.

--#region Enaable triggers

SET @SqlTrigger = '';
SELECT @SqlTrigger = @SqlTrigger + SqlEnableTrigger FROM @triggers;
PRINT @SqlTrigger;
EXEC (@SqlTrigger);

--#endregion Enaable triggers

 

List all triggers, the SchemaName is empty when it is a DDL trigger

SELECT ISNULL(sc.name,'') AS SchemaName,
        ISNULL(tb.name,'(Database)') AS TableName,
        tg.name,
        tg.object_id,
        tg.parent_class,
        tg.parent_class_desc,
        tg.parent_id,
        tg.type,
        tg.type_desc,
        tg.create_date,
        tg.modify_date,
        tg.is_ms_shipped,
        tg.is_disabled,
        tg.is_not_for_replication,
        tg.is_instead_of_trigger,
        OBJECTPROPERTY(tg.object_id, 'ExecIsUpdateTrigger') AS isupdate,
        OBJECTPROPERTY(tg.object_id, 'ExecIsDeleteTrigger') AS isdelete,
        OBJECTPROPERTY(tg.object_id, 'ExecIsInsertTrigger') AS isinsert,
        OBJECTPROPERTY(tg.object_id, 'ExecIsAfterTrigger') AS isafter,
        OBJECTPROPERTY(tg.object_id, 'ExecIsInsteadOfTrigger') AS isinsteadof
FROM sys.schemas AS sc
      INNER JOIN sys.tables AS tb ON sc.schema_id = tb.schema_id
      RIGHT OUTER JOIN sys.triggers AS tg ON tb.object_id = tg.parent_id;

 

image


Source :
https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-triggers-transact-sql?view=sql-server-ver15

Audit jobs

Rédigé par Sozezzo - - Aucun commentaire

A jobSql Job was changed. The questions are: When? by Who?

We suppose you always have a copy of yours jobs.

 

** Check the SQL Script for shared folder, your e-mail and SMTP server.

 

Delete old audit configuration – make sure you want to do that.


print @@servername

USE [master]

GO

BEGIN TRY ALTER SERVER AUDIT SPECIFICATION [ServerAuditSpecification] WITH (STATE = OFF); END TRY BEGIN CATCH END CATCH;

BEGIN TRY DROP  SERVER AUDIT SPECIFICATION [ServerAuditSpecification];                    END TRY BEGIN CATCH END CATCH;

GO

BEGIN TRY ALTER SERVER AUDIT [ServerAudit] WITH (STATE = OFF); END TRY BEGIN CATCH END CATCH;

BEGIN TRY DROP  SERVER AUDIT [ServerAudit];                    END TRY BEGIN CATCH END CATCH;

GO

--xp_cmdshell 'DIR L:Audit*'

exec xp_cmdshell 'MD C:Audit*';

exec xp_cmdshell 'DEL C:Audit*.sqlaudit';

GO

 

 

Create an Audit on a network share


PRINT '-- Step 1 - Create an Audit on a network share'

USE [master];

GO

CREATE SERVER AUDIT [ServerAudit]

TO FILE

(     FILEPATH  = N'C:Audit'

     ,MAXSIZE   = 100 MB

     ,MAX_FILES = 3500

     ,RESERVE_DISK_SPACE = OFF

)

WITH

(     QUEUE_DELAY = 1000

     ,ON_FAILURE  = CONTINUE

)

GO

Go

PRINT '-- Step 2 - Enable the Audit'

ALTER SERVER AUDIT [ServerAudit] WITH (STATE=ON);

Go

Go

-- Step 3 - Create a Server Audit Specification

CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification] FOR SERVER AUDIT [ServerAudit]

--ADD (APPLICATION_ROLE_CHANGE_PASSWORD_GROUP),

--ADD (AUDIT_CHANGE_GROUP),

--ADD (BACKUP_RESTORE_GROUP),

--ADD (BROKER_LOGIN_GROUP),

--ADD (DATABASE_CHANGE_GROUP),

--ADD (DATABASE_LOGOUT_GROUP),

--ADD (DATABASE_MIRRORING_LOGIN_GROUP),

--ADD (DATABASE_OBJECT_ACCESS_GROUP),

--ADD (DATABASE_OBJECT_CHANGE_GROUP),

--ADD (DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP),

--ADD (DATABASE_OBJECT_PERMISSION_CHANGE_GROUP),

--ADD (DATABASE_OPERATION_GROUP),

--ADD (DATABASE_OWNERSHIP_CHANGE_GROUP),

--ADD (DATABASE_PERMISSION_CHANGE_GROUP),

--ADD (DATABASE_PRINCIPAL_CHANGE_GROUP),

--ADD (DATABASE_PRINCIPAL_IMPERSONATION_GROUP),

--ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),

--ADD (DBCC_GROUP),

--ADD (FAILED_DATABASE_AUTHENTICATION_GROUP),

--ADD (FAILED_LOGIN_GROUP),

--ADD (FULLTEXT_GROUP),

--ADD (LOGIN_CHANGE_PASSWORD_GROUP),

--ADD (LOGOUT_GROUP),

--ADD (SCHEMA_OBJECT_ACCESS_GROUP),

--ADD (SCHEMA_OBJECT_CHANGE_GROUP),

--ADD (SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP),

--ADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP),

--ADD (SERVER_OBJECT_CHANGE_GROUP),

--ADD (SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP),

--ADD (SERVER_OBJECT_PERMISSION_CHANGE_GROUP),

--ADD (SERVER_OPERATION_GROUP),

--ADD (SERVER_PERMISSION_CHANGE_GROUP),

--ADD (SERVER_PRINCIPAL_CHANGE_GROUP),

--ADD (SERVER_PRINCIPAL_IMPERSONATION_GROUP),

--ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),

--ADD (SERVER_STATE_CHANGE_GROUP),

--ADD (SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP),

--ADD (SUCCESSFUL_LOGIN_GROUP),

--ADD (TRACE_CHANGE_GROUP),

--ADD (USER_CHANGE_PASSWORD_GROUP),

ADD (USER_DEFINED_AUDIT_GROUP)

WITH (STATE = ON);

GO

 

Log or send e-mail when a job was changed.


 

PRINT '-- Create/Update trigger [trig_sysjobs_log] ON [dbo].[sysjobs]'
USE [msdb]
GO
BEGIN TRY DROP TRIGGER [dbo].[trig_sysjobs_log]; END TRY BEGIN CATCH END CATCH;
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Description:    Audit/Log jobs changements
-- =============================================
CREATE TRIGGER [dbo].[trig_sysjobs_log]
ON [dbo].[sysjobs]
FOR INSERT, UPDATE, DELETE
AS
BEGIN
     SET NOCOUNT ON

--#region DECLARE variables

    DECLARE @bodyAudit nvarchar(4000);
     DECLARE @bodyEvent nvarchar(255);
     DECLARE @bodyEmail nvarchar(max);
     DECLARE @subjectEmail nvarchar(255);
    
     DECLARE @activity       varchar(20)
     DECLARE @jobName        varchar(255);
     DECLARE @jobNameDeleted varchar(255);
     SET @JobName = '';
     SET @jobNameDeleted = '';

    DECLARE @systemUser varchar(100)
     DECLARE @datelog          VARCHAR(50);
     DECLARE @SUSER_ID         varchar(20);
     DECLARE @originalLogin    nvarchar(256);
     DECLARE @hostname    nvarchar(128);
     DECLARE @programName nvarchar(max);

--#endregion DECLARE variables

--#region Create message

    SET @systemUser = SYSTEM_USER;
     SET @datelog    = GETUTCDATE();
     SET @SUSER_ID   = CAST(SUSER_ID() as varchar(20));
     SET @originalLogin   = ORIGINAL_LOGIN();
     SET @hostname   = HOST_NAME();
     SET @programName= PROGRAM_NAME();

    if exists(SELECT * from inserted)
     begin
         if exists (SELECT * from deleted)
         BEGIN
             SET @activity = 'UPDATE';
             SELECT @jobNameDeleted = name from deleted;
         END
         else
         BEGIN
             SET @activity = 'INSERT';
         END
         SELECT @jobName = name from inserted;
     end
     else
     begin
         SET @activity = 'DELETE';
         SELECT @jobName = name from deleted;
     end
    
     -- Audit
     SET @bodyAudit = 'AuditJob' + CHAR(13) + CHAR(10);;
     SET @bodyAudit = @bodyAudit + N'-Job : ' + @JobName + CHAR(13) + CHAR(10);
     IF ( @jobNameDeleted NOT IN ( @JobName , '') ) SET @bodyAudit = @bodyAudit + N'-Job Deleted : ' + @jobNameDeleted + CHAR(13) + CHAR(10);
     SET @bodyAudit = @bodyAudit + N'-Activity : ' + @activity + CHAR(13) + CHAR(10);
     SET @bodyAudit = @bodyAudit + N'-Original Login : ' + @originalLogin + CHAR(13) + CHAR(10);   
     IF (@originalLogin<>@systemUser) SET @bodyAudit = @bodyAudit + N'-System User : ' + @systemUser + CHAR(13) + CHAR(10);
     SET @bodyAudit = @bodyAudit + N'-Date UTC : ' + @datelog + CHAR(13) + CHAR(10);
     SET @bodyAudit = @bodyAudit + N'-Host name : ' + @hostname + CHAR(13) + CHAR(10);
     SET @bodyAudit = @bodyAudit + N'-SUSER_ID : ' + @SUSER_ID + CHAR(13) + CHAR(10);
     SET @bodyAudit = @bodyAudit + N'-Program Name : ' + @programName + CHAR(13) + CHAR(10);
     BEGIN TRY
         EXEC sp_audit_write @user_defined_event_id =  169 , @succeeded =  0, @user_defined_information = @bodyAudit;
     END TRY BEGIN CATCH END CATCH;

--#endregion Create message

--#region Send email

    -- Check if database mail service is configurated with a default profil
     IF (EXISTS ( SELECT prinprof.is_default
                  FROM msdb.sys.database_principals dbprin, msdb.dbo.sysmail_principalprofile prinprof, msdb.dbo.sysmail_profile prof
                  WHERE dbprin.principal_id = msdb.dbo.get_principal_id(prinprof.principal_sid) AND
                  prof.profile_id = prinprof.profile_id    AND prinprof.is_default = 1
                )
        )
     BEGIN
         -- Send email
         BEGIN TRY

            SET @bodyEmail = @bodyAudit;
             SET @subjectEmail = @activity+ ' job ['+ @jobName + ']'
             EXEC msdb.dbo.sp_send_dbmail
                 @recipients        = 'Youremail@mydomain.com',
                 @subject        = @subjectEmail,
                 @body            = @bodyEmail,
                 @from_address = 'noreplay@mydomain.com',
                 @reply_to = 'noreplay@mydomain.com';

        END TRY BEGIN CATCH END CATCH;
     END
--#endregion Send email

--#region Create Event Log

    SET @bodyEvent = REPLACE(@bodyAudit, '   ',' ');
     SET @bodyEvent = REPLACE(@bodyAudit, '  ',' ');
     SET @bodyEvent = SUBSTRING(@bodyEvent,0, 255);

    RAISERROR (@bodyEvent, -- Message text.
            10, -- Severity,
            1 -- State,
            ) WITH LOG;
    
--#endregion Create Event Log

END
GO
GO

 

Check your audit


SELECT top 10 * FROM sys.fn_get_audit_file('C:Audit*', NULL, NULL) ORDER BY 1 DESC;

 

 

Sources:

https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/create-a-server-audit-and-server-audit-specification?view=sql-server-ver15

Monitoring database size files before blow up

Rédigé par Sozezzo - - Aucun commentaire

you are already in trouble and it is too late when ...

  • The transaction log becomes full, SQL Server Database Engine issues a 9002 error.
  • The SQL Server Database Filegroup is Full.
  • Run out of disk space


You must have the answer to theses questions:

  • Who many times the database base can still growth?
  • Do we use the best Practices for Growing Database Files?

Growing database files by a percentage is relatively harmless when databases are small.
Once you’ve grown beyond a few gigabytes, a ten percent file growth can be a cause of performance problems while SQL Server has to wait for space to be allocated.

At the other extreme, for databases over a few gigabytes, it doesn’t make sense to keep growing in tiny 1 MB increments where you can run into extreme physical fragmentation.

Generally, it is rather to grow in increments than in percentages.


Script to list database files that use percent growth, you do not need to change, you just need to be sure it’s okay.

SELECT d.name as database_name,     mf.name as file_name,     mf.type_desc as file_type,     mf.growth as current_percent_growth
FROM sys.master_files mf (NOLOCK)
JOIN sys.databases d (NOLOCK) on mf.database_id=d.database_id
WHERE is_percent_growth=1



Script to monitoring size files.

DECLARE @growth_count INT= 3;  -- Who many time the database can growth ?
SET NOCOUNT ON;
--#region Local drives
-- DROP TABLE IF EXISTS #svr_drives;
CREATE TABLE #svr_drives
(     DriveLetter CHAR(1),      TotalSpace  BIGINT,      FreeSpace   BIGINT
);
-- Populate temp table with free space of all local drives
INSERT INTO #svr_drives
SELECT DISTINCT         SUBSTRING(volume_mount_point, 1, 1) AS DriveLetter,         total_bytes / 1024 / 1024 AS TotalSpace,         available_bytes / 1024 / 1024 AS FreeSpace
FROM sys.master_files AS f      CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id);
--select * from #svr_drives
--#endregion Local drives
--#region create sysaltfiles
-- DROP TABLE IF EXISTS #sysaltfiles;
CREATE TABLE #sysaltfiles
(     [dbname]               [NVARCHAR](128) NULL,      [fileid]               [SMALLINT] NULL,      [FileSize]             [BIGINT] NULL,      [TotalSpace]           [BIGINT] NULL,      [FreeSpace]            [BIGINT] NULL,      [maxsize]              [INT] NOT NULL,      [status]               [INT] NULL,      [dbid]                 [SMALLINT] NULL,      [filename]             [NVARCHAR](260) NOT NULL,      [Usage]                [VARCHAR](4) NOT NULL,      [DriveLetter]          [NVARCHAR](1) NULL,      [IsgrowthInMB]         [INT] NOT NULL,      [growth]               [INT] NOT NULL,      [growth_count]         [INT],      [WillGrowth]           [BIGINT] NULL,      [WillMissingSpaceDisk] INT,      [NearFileSizeLimit]    INT,      [Message]              NVARCHAR(MAX)
);
INSERT INTO #sysaltfiles
(dbname,   fileid,   FileSize,   TotalSpace,   FreeSpace,   maxsize,   [status],   [dbid],   [filename],   Usage,   DriveLetter,   IsgrowthInMB,   growth
)
SELECT DB_NAME(dbid) dbname,         s.fileid,         (CONVERT(BIGINT, s.[size]) * 8) / 1024 AS FileSize,         TotalSpace,         FreeSpace AS FreeSpace,         maxsize / 128 AS maxsize,         [status],         [dbid],         [filename],        CASE STATUS&0x40            WHEN 0x40            THEN 'log'            ELSE 'data'        END AS 'Usage',         SUBSTRING([filename], 1, 1) AS DriveLetter,        CASE STATUS&0x100000            WHEN 0x100000            THEN 0            ELSE-1        END AS IsgrowthInMB,         growth
FROM sys.sysaltfiles s      INNER JOIN #svr_drives d ON SUBSTRING([filename], 1, 1) = d.DriveLetter
WHERE DATABASEPROPERTYEX(DB_NAME(dbid), 'Status') = 'ONLINE'       AND DATABASEPROPERTYEX(DB_NAME(dbid), 'Updateability') <> 'READ_ONLY'       AND DB_NAME(dbid) NOT IN('model'); -- we can add a filter.
--#endregion create sysaltfiles
--#region Message
UPDATE #sysaltfiles   SET        growth_count = @growth_count,        [WillGrowth] = CASE                          WHEN IsgrowthInMB = 0                          THEN FileSize * (POWER(1 + ([growth] / 100.0), @growth_count) - 1)                          ELSE(growth / 128) * @growth_count                      END,        WillMissingSpaceDisk = CASE                                  WHEN CASE                                           WHEN IsgrowthInMB = 0                                           THEN FileSize * (POWER(1 + ([growth] / 100.0), @growth_count) - 1)                                           ELSE(growth / 128) * @growth_count                                       END > FreeSpace                                  THEN-1                                  ELSE 0                              END,        NearFileSizeLimit = CASE                               WHEN [maxsize] > 0                                    AND CASE                                            WHEN IsgrowthInMB = 0                                            THEN FileSize * (POWER(1 + ([growth] / 100.0), @growth_count))                                            ELSE FileSize + (growth / 128) * @growth_count                                        END > [maxsize]                               THEN-1                               ELSE 0                           END,        [Message] = ''
FROM #sysaltfiles;
UPDATE #sysaltfiles   SET        [Message] = [Message] + CASE                                   WHEN WillMissingSpaceDisk = -1                                   THEN 'The database [' + dbname + '] will missing space disk when the file [' + [filename] + '] will growth (' + CAST([WillGrowth] / 1024 AS NVARCHAR(20)) + ' GB) after ' + CAST(growth_count AS NVARCHAR(20)) + ' "growths" on disk (' + [DriveLetter] + ':) ' + CAST([FreeSpace] / 1024 AS NVARCHAR(20)) + ' GB Free of ' + CAST([TotalSpace] / 1024 AS NVARCHAR(20)) + ' GB.' + CHAR(13) + CHAR(10)                                   ELSE ''                               END; 
UPDATE #sysaltfiles   SET        [Message] = [Message] + CASE                                   WHEN NearFileSizeLimit = -1                                   THEN 'The database [' + dbname + '] is near of file size limit for the file [' + [filename] + ']. Using ' + CAST(FileSize AS NVARCHAR(20)) + ' GB of max ' +CAST(maxsize AS NVARCHAR(20)) + ' GB' + CHAR(13) + CHAR(10)                                   ELSE ''                               END;
DECLARE @Message NVARCHAR(MAX)= '';
SELECT @Message = @Message + [Message]
FROM #sysaltfiles
WHERE [Message] <> ''
ORDER BY dbname;
-- SELECT * FROM #sysaltfiles;
--#endregion Message
PRINT @Message;



If you are using a job to monitoring, you can add this script to send an e-mail.

--#region Send email
IF(@Message <> '')
BEGIN
    DECLARE @body NVARCHAR(MAX)= '<p>The database server [servername] will have space disk problem. </p><p/><p/><p>[Message]</p><p/>This message was sent by [JobName] AT [servername]';
    DECLARE @jobname SYSNAME, @jobid UNIQUEIDENTIFIER;
    SELECT @jobname = b.name,             @jobid = b.job_id     FROM sys.dm_exec_sessions a,           msdb.dbo.sysjobs b     WHERE a.session_id = @@spid           AND (SUBSTRING(MASTER.dbo.FN_VARBINTOHEXSTR(CONVERT(VARBINARY(16), b.JOB_ID)), 1, 10)) = SUBSTRING(a.PROGRAM_NAME, 30, 10);     PRINT 'Job Name : ' + @jobname;
    SET @body = REPLACE(@body, '[JobName]', @jobname);     SET @body = REPLACE(@body, '[servername]', @@SERVERNAME);     SET @body = REPLACE(@body, '[Message]', @Message);
    EXEC msdb.dbo.sp_send_dbmail           @recipients = 'user@yourdomain.com',           @subject = 'Warning : Monitoring size file database',           @body = @body,           @body_format = 'HTML',           @from_address = 'Sql Server <sender@yourdomain.com>',           @reply_to = 'sender@yourdomain.com';
END;
--#endregion Send email

Get job name on fly

Rédigé par Sozezzo - - Aucun commentaire

Get Job name when you are executing job. This is useful when you need to make reference to the job being executed.

 


DECLARE @jobname sysname, @jobid uniqueidentifier

SELECT @jobname=b.name,@jobid=b.job_id 
FROM sys.dm_exec_sessions a,msdb.dbo.sysjobs b
WHERE a.session_id=@@spid
AND
(SUBSTRING(MASTER.dbo.FN_VARBINTOHEXSTR(CONVERT(VARBINARY(16), b.JOB_ID)),1,10)) = SUBSTRING(a.PROGRAM_NAME,30,10)
PRINT 'Job Name : ' + @jobname

 

Instead, you can Use Tokens in Job Steps.
https://docs.microsoft.com/en-us/sql/ssms/agent/use-tokens-in-job-steps


PRINT 'Job Name  : '+'$(ESCAPE_SQUOTE(JOBNAME))'
PRINT 'Step Name : '+'$(ESCAPE_SQUOTE(STEPNAME))'

 

List all table constraints (PK, UK, FK, Check and Default) in SQL Server

Rédigé par Sozezzo - - Aucun commentaire

 

Query lists all table (and view) constraints - primary keys, unique key constraints and indexes, foreign keys and check, default constraints, and isDisabled.

 

Columns

  • SchemaName
  • Name - table or view schema and name
  • object_type - object type:
    • Table
    • View
  • constraint_type - type of constraint:
    • Primary key
    • Unique key
    • Foregin key
    • Check constraint
    • Default constraint
  • constraint_name - name of constraint or index
  • details - details of this constraint:
    • Primary key - PK column(s)
    • Unique key - UK column(s)
    • Foregin key - parent table name
    • Check constraint - check definition
    • Default constraint - column name and default value definition
  • IsDisable – it is disabled or not.

 


SELECT SchemaName
     ,[Name]
     ,object_type
     ,constraint_type
     ,constraint_name
     ,details
     ,IsDisabled
FROM (
     -- Index
     SELECT SCHEMA_NAME(t.schema_id) AS SchemaName
         ,t.[name] AS [Name]
         ,CASE
             WHEN t.[type] = 'U'
                 THEN 'Table'
             WHEN t.[type] = 'V'
                 THEN 'View'
             END AS [object_type]
         ,CASE
             WHEN c.[type] = 'PK'
                 THEN 'Primary key'
             WHEN c.[type] = 'UQ'
                 THEN 'Unique constraint'
             WHEN i.[type] = 1
                 THEN 'Unique clustered index'
             WHEN i.type = 2
                 THEN 'Unique index'
             END AS constraint_type
         ,ISNULL(c.[name], i.[name]) AS constraint_name
         ,SUBSTRING(column_names, 1, LEN(column_names) - 1) AS [details]
         ,i.is_disabled AS IsDisabled
     FROM sys.objects t
     LEFT OUTER JOIN sys.indexes i ON t.object_id = i.object_id
     LEFT OUTER JOIN sys.key_constraints c ON i.object_id = c.parent_object_id
         AND i.index_id = c.unique_index_id
     CROSS APPLY (
         SELECT col.[name] + ', '
         FROM sys.index_columns ic
         INNER JOIN sys.columns col ON ic.object_id = col.object_id
             AND ic.column_id = col.column_id
         WHERE ic.object_id = t.object_id
             AND ic.index_id = i.index_id
         ORDER BY col.column_id
         FOR XML PATH('')
         ) D(column_names)
     WHERE is_unique = 1
         AND t.is_ms_shipped <> 1
    
     UNION ALL
    
     -- Foreign key
     SELECT SCHEMA_NAME(fk_tab.schema_id)
         ,fk_tab.NAME AS foreign_table
         ,'Table'
         ,'Foreign key'
         ,fk.NAME AS fk_constraint_name
         ,SCHEMA_NAME(pk_tab.schema_id) + '.' + pk_tab.NAME
         ,fk.is_disabled AS IsDisabled
     FROM sys.foreign_keys fk
     INNER JOIN sys.tables fk_tab ON fk_tab.object_id = fk.parent_object_id
     INNER JOIN sys.tables pk_tab ON pk_tab.object_id = fk.referenced_object_id
     INNER JOIN sys.foreign_key_columns fk_cols ON fk_cols.constraint_object_id = fk.object_id
    
     UNION ALL
    
     -- Check constraint
     SELECT SCHEMA_NAME(t.schema_id)
         ,t.[name]
         ,'Table'
         ,'Check constraint'
         ,con.[name] AS constraint_name
         ,con.[definition]
         ,con.is_disabled AS IsDisabled
     FROM sys.check_constraints con
     LEFT OUTER JOIN sys.objects t ON con.parent_object_id = t.object_id
     LEFT OUTER JOIN sys.all_columns col ON con.parent_column_id = col.column_id
         AND con.parent_object_id = col.object_id
    
     UNION ALL
    
     -- Default constraint
     SELECT SCHEMA_NAME(t.schema_id)
         ,t.[name]
         ,'Table'
         ,'Default constraint'
         ,con.[name]
         ,col.[name] + ' = ' + con.[definition]
         ,0 AS IsDisabled
     FROM sys.default_constraints con
     LEFT OUTER JOIN sys.objects t ON con.parent_object_id = t.object_id
     LEFT OUTER JOIN sys.all_columns col ON con.parent_column_id = col.column_id
         AND con.parent_object_id = col.object_id
     ) a
ORDER BY SchemaName
     ,[Name]
     ,constraint_type
     ,constraint_name;

 

 

Script to set nocheck on foreign key constaints


-- Script to set nocheck on foreign key constaints
DECLARE @SchemaName VARCHAR(250) = NULL; -- 'dbo';
DECLARE @TableName VARCHAR(250) = NULL;  -- 'Client';
DECLARE @IDKey INT;
DECLARE @SqlScript VARCHAR(max);

SET NOCOUNT ON;

DROP TABLE IF EXISTS #tb;

-- Foreign key
SELECT ROW_NUMBER() OVER(ORDER BY SCHEMA_NAME(fk_tab.schema_id), fk_tab.NAME) AS [IDKey],
        --SCHEMA_NAME(fk_tab.schema_id),
        --fk_tab.NAME AS foreign_table,
        --fk.NAME AS fk_constraint_name,
        --SCHEMA_NAME(pk_tab.schema_id) + '.' + pk_tab.NAME,
        --fk.is_disabled AS IsDisabled,
        'ALTER TABLE [' + SCHEMA_NAME(fk_tab.schema_id) + '].[' + fk_tab.NAME + '] NOCHECK CONSTRAINT ' + fk.NAME + ';' AS SqlScript
INTO #tb
FROM sys.foreign_keys fk
      INNER JOIN sys.tables fk_tab ON fk_tab.object_id = fk.parent_object_id
      INNER JOIN sys.tables pk_tab ON pk_tab.object_id = fk.referenced_object_id
      INNER JOIN sys.foreign_key_columns fk_cols ON fk_cols.constraint_object_id = fk.object_id
WHERE fk.is_disabled = 0
AND (@SchemaName IS NULL OR @SchemaName = SCHEMA_NAME(fk_tab.schema_id))
AND (@TableName IS NULL OR @TableName = fk_tab.NAME)
;

while ((SELECT COUNT(*) FROM #tb) > 0 )
BEGIN
     SELECT top 1 @IDKey=IDKey, @SqlScript = SqlScript FROM #tb ORDER BY IDKey;
     DELETE FROM #tb WHERE @IDKey=IDKey;
     PRINT @SqlScript;
     --EXEC(@SqlScript);
END;

Fil RSS des articles de cette catégorie