LearnItFirst User Forum

CREATE TABLE dbo.SequenceNumbers(SeqNo int IDENTITY(1,1) NOT NULL CONSTRAINT PK_SequenceNumbers PRIMARY KEY)
GO
DECLARE @i INT
SET @i = 0
WHILE @I<8000
    BEGIN
        INSERT SequenceNumbers DEFAULT VALUES
        SET @i = @i + 1
    END
GO
CREATE FUNCTION dbo.Split ( 
@Array VARCHAR(MAX) 
, @Delimiter CHAR(1) = ',' 
)   
RETURNS TABLE 
AS 
/* 
Basically a split function that works against an array. This is from Erland Sommarskog -- http://www.sommarskog.se/arrays-in-sql.html 
*/ 
RETURN (
    SELECT SeqNo - LEN(REPLACE(LEFT(@Array, SeqNo), ',', '')) + 1 AS Position, LTRIM(SUBSTRING(@Array, SeqNo, CHARINDEX(',', @Array + ',', SeqNo) - SeqNo)) AS Element 
    FROM dbo.SequenceNumbers   
    WHERE SeqNo <= LEN(@Array) AND SUBSTRING(',' + @Array, SeqNo, 1) = @Delimiter 
) 
GO
CREATE SCHEMA dba
GO
CREATE PROC dba.GetObjectsInSchema (
    @SchemaName SYSNAME
    , @ObjectType CHAR(2) = NULL
    , @ObjectTypesToExclude NVARCHAR(128) = 'PK, UQ, C, D, F' -- array of object types to exclude; default to leaving out the column-level objects
)
AS
IF @ObjectType IS NULL
    SELECT SCHEMA_NAME(schema_id) AS SchemaName, name, object_id, principal_id, schema_id, parent_object_id, type, type_desc, create_date, modify_date, is_ms_shipped
    FROM sys.objects
    WHERE SCHEMA_NAME(schema_id) = @SchemaName
        AND Type NOT IN (SELECT Element COLLATE Latin1_General_CI_AS_KS_WS FROM dbo.Split(@ObjectTypesToExclude, ','))
    ORDER BY name, type
ELSE
    SELECT SCHEMA_NAME(schema_id) AS SchemaName, name, object_id, principal_id, schema_id, parent_object_id, type, type_desc, create_date, modify_date, is_ms_shipped
    FROM sys.objects
    WHERE SCHEMA_NAME(schema_id) = @SchemaName
        AND type=@ObjectType
        AND Type NOT IN (SELECT Element COLLATE Latin1_General_CI_AS_KS_WS FROM dbo.Split(@ObjectTypesToExclude, ','))
    ORDER BY name