MD3.f_GetConstraints.sql 2.46 KB
Newer Older
dwentzel's avatar
dwentzel committed
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82
if exists (select * from dbo.sysobjects where id = object_id(N'MD3.f_GetConstraints'))
drop function MD3.f_GetConstraints
GO

CREATE  function MD3.f_GetConstraints( 
    @SchemaName SYSNAME
   ,@TblName SYSNAME 

)
RETURNS @Table  TABLE   
(    
    TableName	varchar(255) NOT NULL,     
    SchemaName varchar(255) NOT NULL,
    ConstraintName varchar(255) NOT NULL,
    ConstraintExpression varchar(max) NOT NULL,
    ColumnName varchar(255) NULL,
    is_system_named BIT NOT NULL,  
    is_not_trusted	BIT NOT NULL,
    ConstraintType varchar(255) NOT NULL
    
)    
AS  
BEGIN

    DECLARE @FullTblName SYSNAME
	SELECT @FullTblName = @SchemaName + '.' + @TblName

		INSERT INTO @Table
		--column level constraints
        SELECT 
		    OBJECT_NAME(parent_object_id) AS TableName,
		    OBJECT_SCHEMA_NAME(parent_object_id) AS SchemaName,
		    OBJECT_NAME(cc.object_id) AS ConstraintName,
		    cc.definition AS ConstraintExpression,
		    cols.name AS ColumnName,
		    cc.is_system_named ,
		    cc.is_not_trusted,
		    'CHECK' AS ConstraintType
	    FROM sys.check_constraints(NOLOCK) cc
		JOIN sys.columns(NOLOCK) cols 
		    ON cc.parent_column_id = cols.column_id
		    AND cc.parent_object_id = cols.object_id
	     WHERE cc.parent_object_id = object_id(@FullTblName)	
	
	    UNION ALL 
		--table level constraints
	     SELECT 
		    OBJECT_NAME(parent_object_id) AS TableName,
		    OBJECT_SCHEMA_NAME(parent_object_id) AS SchemaName,
		    OBJECT_NAME(cc.object_id) AS ConstraintName,
		    cc.definition AS CheckConstraintExpression,
		    NULL AS ColumnName,
		    cc.is_system_named ,
		    cc.is_not_trusted,
		    'CHECK' AS ConstraintType
	    FROM sys.check_constraints(NOLOCK) cc
	    WHERE cc.parent_object_id = object_id(@FullTblName)	
			AND cc.parent_column_id = 0
		
	    UNION ALL 
	   --default constraints	       
	   SELECT 
		    OBJECT_NAME(parent_object_id) AS TableName,
		    OBJECT_SCHEMA_NAME(parent_object_id) AS SchemaName,
		    OBJECT_NAME(cc.object_id) AS ConstraintName,
		    cc.definition AS CheckConstraintExpression,
		    cols.name AS ColumnName,
		    cc.is_system_named ,
		    0,
		    'DEFAULT' AS ConstraintType
	    FROM sys.default_constraints(NOLOCK) cc
		JOIN sys.columns(NOLOCK) cols 
		    ON cc.parent_column_id = cols.column_id
		    AND cc.parent_object_id = cols.object_id
	     WHERE cc.parent_object_id = object_id(@FullTblName)	
    
  
   
   RETURN
  
END    
GO