프로시저 및 펑션 생성 스크립트 > db

2005에서만 동작함

SELECT
SCHEMA_NAME(sp.schema_id) AS [Schema],
sp.name AS [Name],
CASE WHEN sp.type = N'P' THEN 1 WHEN sp.type = N'PC' THEN 2 ELSE 1 END AS [ImplementationType],
CAST(OBJECTPROPERTYEX(sp.object_id,N'ExecIsQuotedIdentOn') AS bit) AS [QuotedIdentifierStatus],
CAST(OBJECTPROPERTYEX(sp.object_id,N'ExecIsAnsiNullsOn') AS bit) AS [AnsiNullsStatus],
NULL AS [Text],
CAST(
 case
    when sp.is_ms_shipped = 1 then 1
    when (
        select
            major_id
        from
            sys.extended_properties
        where
            major_id = sp.object_id and
            minor_id = 0 and
            class = 1 and
            name = N'microsoft_database_tools_support')
        is not null then 1
    else 0
end
             AS bit) AS [IsSystemObject],
sp.object_id AS [ID],
CAST(ISNULL(spp.is_auto_executed,0) AS bit) AS [Startup],
ISNULL(smsp.definition, ssmsp.definition) AS [Definition]
FROM
sys.all_objects AS sp
LEFT OUTER JOIN sys.procedures AS spp ON spp.object_id = sp.object_id
LEFT OUTER JOIN sys.sql_modules AS smsp ON smsp.object_id = sp.object_id
LEFT OUTER JOIN sys.system_sql_modules AS ssmsp ON ssmsp.object_id = sp.object_id
WHERE
(sp.type = N'P' OR sp.type = N'RF' OR sp.type='PC')
ORDER BY
[Schema] ASC,[Name] ASC

SELECT
SCHEMA_NAME(udf.schema_id) AS [Schema],
udf.name AS [Name],
CASE WHEN udf.type IN ('FN','IF','TF') THEN 1 WHEN udf.type IN ('FS','FT') THEN 2 ELSE 1 END AS [ImplementationType],
udf.object_id AS [ID],
CAST(
 case
    when udf.is_ms_shipped = 1 then 1
    when (
        select
            major_id
        from
            sys.extended_properties
        where
            major_id = udf.object_id and
            minor_id = 0 and
            class = 1 and
            name = N'microsoft_database_tools_support')
        is not null then 1
    else 0
end
             AS bit) AS [IsSystemObject],
CAST(OBJECTPROPERTYEX(udf.object_id, N'IsQuotedIdentOn') AS bit) AS [QuotedIdentifierStatus],
CAST(OBJECTPROPERTYEX(udf.object_id,N'ExecIsAnsiNullsOn') AS bit) AS [AnsiNullsStatus],
NULL AS [Text],
ISNULL(smudf.definition, ssmudf.definition) AS [Definition]
FROM
sys.all_objects AS udf
LEFT OUTER JOIN sys.sql_modules AS smudf ON smudf.object_id = udf.object_id
LEFT OUTER JOIN sys.system_sql_modules AS ssmudf ON ssmudf.object_id = udf.object_id
WHERE
(udf.type in ('TF', 'FN', 'IF', 'FS', 'FT'))
ORDER BY [Schema] ASC,[Name] ASC


출처 : http://cafe.naver.com/sqlmvp.cafe?iframe_url=/ArticleRead.nhn%3Farticleid=255