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

본문 바로가기

db

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

페이지 정보

작성자 서방님 댓글 0건 조회 19회 작성일 08-11-06 11:14

본문

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

댓글목록

등록된 댓글이 없습니다.

Total 447건 1 페이지
게시물 검색

회원로그인

접속자집계

오늘
139
어제
365
최대
592
전체
40,113

그누보드5
Copyright © seobangnim.com All rights reserved.
자바스크립트를 활성화 하세요![ 브라우저에서 자바스크립트를 활성화하는 방법 ]