Script foreign keys related to main primary table

by Amol 15. February 2011 18:28

You cannot do some operations if you have foreign keys attached to primary table.  For example, you cannot truncate table, you cannot modify column data type. To do such operation we need to drop foreign keys first and then recreate the same.  Manually finding all the foreign keys are time consuming process. To avoid manual interaction I have created script to print out all the Keys related to the main primary table and its child tables.

WITH ParentTable 
AS
(
    SELECT '[dbo].[Catalog]' AS ObjectName  
)
,MainQuery
AS
(
    SELECT
fkc.Constraint_Object_Id,
        QUOTENAME(OBJECT_NAME(fkc.constraint_object_id)) AS ConstraintName,
        QUOTENAME(Objectsc.name) AS SchemaName,
        QUOTENAME(OBJECT_NAME(fkc.parent_object_id)) AS ObjectName,
        QUOTENAME(ObjectColumn.name) AS ColumnName,
        QUOTENAME(ObjectColumnType.name) AS ColumnType,      
QUOTENAME(referencedSc.name) AS ReferencedSchemaName,
                   
QUOTENAME(OBJECT_NAME(fkc.referenced_object_id)) AS ReferencedObjectName,      
QUOTENAME(ReferencedColumn.name) AS ReferencedColumnName,
      
QUOTENAME(ReferencedObjectColumnType.name) AS ReferencedObjectColumnType

    FROM sys.foreign_key_columns fkc
    INNER JOIN sys.columns ObjectColumn
        ON (fkc.parent_object_id = ObjectColumn.object_id
            AND fkc.parent_column_id = ObjectColumn.column_id)
    INNER JOIN sys.columns ReferencedColumn
        ON (fkc.referenced_object_id = ReferencedColumn.object_id
            AND fkc.referenced_column_id = ReferencedColumn.column_id)
    INNER JOIN sys.types ObjectColumnType
        ON (ObjectColumn.User_type_id = ObjectColumnType.User_type_id)
    INNER JOIN sys.types ReferencedObjectColumnType
        ON (ReferencedColumn.User_type_id = ReferencedObjectColumnType.User_type_id)
    INNER JOIN sys.tables ObjectT
        ON (fkc.parent_object_id = ObjectT.object_id)
    INNER JOIN sys.schemas ObjectSc
        ON (ObjectT.schema_id = ObjectSc.schema_id)
    INNER JOIN sys.tables referencedT
        ON (fkc.parent_object_id = referencedT.object_id)
    INNER JOIN sys.schemas referencedSc
        ON (referencedT.schema_id = referencedSc.schema_id)
)

,CTE

AS
(
    SELECT
        constraint_object_id,
        ConstraintName,
        SchemaName,
        ObjectName,
        ColumnName,
        ColumnType,
        ReferencedSchemaName,
        ReferencedObjectName,
        ReferencedColumnName,
        ReferencedObjectColumnType,
        1 AS Lvl
    FROM MainQuery
    WHERE ReferencedSchemaName + '.' + ReferencedObjectName = (SELECT ObjectName FROM ParentTable)
                      AND SchemaName + '.' + ObjectName <> (SELECT ObjectName FROM ParentTable)

    UNION ALL

    SELECT 
        MainQuery.constraint_object_id,
        MainQuery.ConstraintName,
        MainQuery.SchemaName,
        MainQuery.ObjectName,
        MainQuery.ColumnName,
        MainQuery.ColumnType,
        MainQuery.ReferencedSchemaName,
        MainQuery.ReferencedObjectName,
        MainQuery.ReferencedColumnName,
        MainQuery.ReferencedObjectColumnType,
        CTE.Lvl + 1 AS lvl
    FROM MainQuery
    INNER JOIN CTE
        ON (MainQuery.ReferencedObjectName = CTE.ObjectName )

)

,ALLColumnsInLine

AS

(
    SELECT
        Constraint_object_id,
        ConstraintName,
        SchemaName,
        ObjectName,
        ColumnName =
            STUFF( (
                      SELECT DISTINCT
                      ',' + t2.ColumnName
                      FROM CTE t2
                      WHERE t2.constraint_object_id = t1.constraint_object_id
                      FOR XML PATH('')
                   )
                   ,1
                   ,1
                   ,''
                 ),
        ColumnType =
            STUFF( (
                      SELECT DISTINCT
                      ',' + t2.ColumnType
                      FROM CTE t2
                      WHERE t2.constraint_object_id = t1.constraint_object_id
                      FOR XML PATH('')
                   )
                   ,1
                   ,1
                   ,''
                 ),
        ReferencedSchemaName,
        ReferencedObjectName,
        ReferencedColumnName =
            STUFF( (
                      SELECT DISTINCT
                      ',' + t2.ReferencedColumnName
                      FROM CTE t2
                      WHERE t2.constraint_object_id = t1.constraint_object_id
                      FOR XML PATH('')
                   )
                   ,1
                   ,1
                   ,''
                 ), 
     ReferencedObjectColumnType =
          STUFF( (
                      SELECT DISTINCT
                      ',' + t2.ReferencedObjectColumnType
                      FROM CTE t2
                      WHERE t2.constraint_object_id = t1.constraint_object_id
                      FOR XML PATH('')
                   )
                   ,1
                   ,1
                   ,''
                 ),
      Lvl
    FROM CTE t1
    GROUP BY
        Constraint_object_id,
        ConstraintName,
        SchemaName,
        ObjectName,
        ReferencedSchemaName,
        ReferencedObjectName,
        Lvl
)

,FinalQuery

AS

(

     SELECT *,
        ' IF EXISTS (SELECT * ' + CHAR(13) +
        '           FROM sys.foreign_keys ' + CHAR(13) +
        '           WHERE object_id = OBJECT_ID(N''' + ConstraintName + ''') ' + CHAR(13) +
        '                 AND parent_object_id = OBJECT_ID(N''' + SchemaName + '.' + ObjectName + ''')) ' + CHAR(13) +
        '   ALTER TABLE ' + SchemaName + '.' + ObjectName +
        ' DROP CONSTRAINT ' + ConstraintName + CHAR(10) + CHAR(13)
        + ' GO ' + CHAR(10) + CHAR(13) AS DropStatement,
        ' ALTER TABLE ' + SchemaName + '.' + ObjectName + CHAR(13) +
        ' WITH CHECK ADD CONSTRAINT ' + ConstraintName + CHAR(13) +
        ' FOREIGN KEY(' + ColumnName + ') ' + CHAR(13) +
        ' REFERENCES ' + ReferencedSchemaName + '.' + ReferencedObjectName + ' (' + ReferencedColumnName + ') '
        + CHAR(10) + CHAR(13) + ' GO ' + CHAR(10) + CHAR(13) +
|        ' ALTER TABLE ' + SchemaName + '.' + ObjectName + CHAR(13) + 
        ' CHECK CONSTRAINT ' + ConstraintName
        + CHAR(10) + CHAR(13) + ' GO ' + CHAR(10) + CHAR(13) AS CreateStatement
    FROM ALLColumnsInLine
)

SELECT
    Constraint_object_id,
    ConstraintName,
    SchemaName,
    ObjectName,
    ColumnName,
    ColumnType,
    ReferencedSchemaName,
    ReferencedObjectName,
    ReferencedColumnName,
    ReferencedObjectColumnType,
    DropStatement + CreateStatement AS DropAndCreate
FROM FinalQuery
ORDER BY Lvl DESC, ReferencedObjectName 

Tags:

SQL Server Scripts

Configure Database Mail

by Amol 30. April 2010 12:55
Database Mail is an enterprise solution for sending e-mail messages from the SQL Server Database Engine. Using Database Mail, your database applications can send e-mail messages to users. The messages can contain query results, and can also include files from any resource on your network.
 
Here is the script to configure database mail by creating Database Mail Account and Database Mail Profile.
Replace Placeholders according to your need. For example Replace #EmailId# with valid Email address.
 

Use [msdb]

GO

 

sp_configure 'show advanced options', 1;

GO

RECONFIGURE

GO

sp_configure 'Database Mail XPs', 1;

GO

RECONFIGURE

GO

 

-- Delete Principle profile if exits

BEGIN TRY

 

      EXECUTE msdb.dbo.sysmail_delete_principalprofile_sp

                  @principal_id = 0,

                  @profile_name = '#ProfileName#'

 

END TRY

BEGIN CATCH

 

      PRINT 'Invalid Pronciple Profile'

 

END CATCH

 

GO

 

-- Delete profile account if exits

BEGIN TRY

 

      EXECUTE msdb.dbo.sysmail_delete_profileaccount_sp

                  @profile_name = '#ProfileName#',

                  @account_name = '#AccountName#'

 

END TRY

BEGIN CATCH

 

      PRINT 'Invalid Profile Account'

 

END CATCH

 

GO

 

-- Delete account if exits

BEGIN TRY

 

      EXECUTE msdb.dbo.sysmail_delete_account_sp

              @account_name = '#AccountName#'

 

END TRY

BEGIN CATCH

 

      PRINT 'Invalid Account'

 

END CATCH

 

GO

 

-- Delete profile if exits

BEGIN TRY

 

      EXECUTE msdb.dbo.sysmail_delete_profile_sp

              @profile_name = '#ProfileName#'

 

END TRY

BEGIN CATCH

 

      PRINT 'Invalid Profile'

 

END CATCH

 

GO

 

-- Create a Database Mail account

DECLARE  @emailaddress varchar (100)

        ,@mailservername varchar(128)

 

SET @emailaddress = '#EmailId#'

SET @mailservername = '#MailServerName)'

 

EXECUTE msdb.dbo.sysmail_add_account_sp

    @account_name = '#AccountName#',

    @description = 'Mail account for administrative e-mail.',

    @email_address =  @emailaddress ,

    @display_name = 'Automated Mailer',

    @mailserver_name = @mailservername ,

    @port = 25,

    @use_default_credentials = 1

 

GO

 

-- Create a Database Mail Profile

EXECUTE msdb.dbo.sysmail_add_profile_sp

       @profile_name = '#ProfileName#',

       @description = 'Profile used for mail'

 

 

GO

 

-- Add the account to the profile

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp

    @profile_name = '#ProfileName#',

    @account_name = '#AccountName#',

    @sequence_number = 1

 

GO

 

---- Grant access to the profile to the DBMailUsers role

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp

    @principal_id = 0,

    @profile_name = '#ProfileName#',

    @is_default = 1

 

GO

Tags: ,

SQL Server Scripts

SQL Server Partition Details

by Amol 29. April 2010 12:26

You will find the following query useful if you deal with SQL Server Partitioned objects. I have written this query to know whether paritioned data gets instered into desired partition. Apart from this you will get to know partition boundaries, the filegroup is used to store the data, the number of rows in each partition. Provided query is much faster as it deals with catalong views.

You can use this query to find out the number of rows in table if you have billions of rows instead firing query on actual table which may take long time to execute.


You can just replace '<ObjectName>' with the object you interested in filer criteria.

 

 

SELECT

      i.object_id,

      i.name AS IndexName,

      p.partition_number,

      fg.name AS FileGroupName,

      value,

      ps.name AS SchemaName,

      f.name FunctionName

FROM sys.partitions p

JOIN sys.indexes i

      ON (p.object_id = i.object_id

          AND p.index_id = i.index_id)

JOIN sys.partition_schemes ps

      ON (ps.data_space_id = i.data_space_id)

JOIN sys.partition_functions f

      ON (f.function_id = ps.function_id)

LEFT JOIN sys.partition_range_values rv   

      ON (f.function_id = rv.function_id

          AND p.partition_number = rv.boundary_id)

JOIN sys.destination_data_spaces dds

      ON (dds.partition_scheme_id = ps.data_space_id

          AND dds.destination_id = p.partition_number)

JOIN sys.filegroups fg

      ON (dds.data_space_id = fg.data_space_id)

WHERE i.index_id < 2

      AND i.object_id = Object_Id('<ObjectName>')

Tags: ,

SQL Server Scripts

Tag cloud