- 如题, 有点绕口, 结果就是通过如图的方式实现查看过程等信息.
过程详细:
/*----------------------------------------------------------*/
/* [sp_TextGet] */
/*----------------------------------------------------------*/
IF EXISTS ( SELECT 1 FROM sys.objects o WHERE object_id = object_id( N'[sp_TextGet]' ) AND OBJECTPROPERTY( object_id, N'IsProcedure') = 1 )
DROP PROCEDURE [sp_TextGet]
GO
CREATE PROC [sp_TextGet]
( @ObjectName VARCHAR(256) = NULL )
AS
/*
2019-02-28 19:20
*/
BEGIN
SET NOCOUNT ON
DECLARE @default_schema_name VARCHAR(128)
SELECT @default_schema_name = default_schema_name
FROM sys.database_principals
WHERE name = user_name()
SELECT @ObjectName = RTRIM( LTRIM( @ObjectName ) )
IF( LEFT( @ObjectName, 1 ) = '[' AND RIGHT( @ObjectName, 1 ) = ']' )
BEGIN
SELECT @ObjectName = REPLACE( REPLACE( @ObjectName, '[', '' ), ']', '' )
END
DECLARE @id INT
DECLARE @schemaname VARCHAR(256)
DECLARE @Text VARCHAR(8000)
DECLARE @name VARCHAR(256)
DECLARE @Type VARCHAR(256)
DECLARE @temp VARCHAR(8000)
DECLARE @pos INT, @pos1 INT
DECLARE name_cursor CURSOR FOR
SELECT schema_name( o.schema_id ),
o.object_id,
o.name,
o.type
FROM sys.all_objects o
WHERE ( ( o.type = 'P' ) OR
( o.type = 'FN' ) OR
( o.type = 'IF' ) OR
( o.type = 'TF' ) OR
( o.type = 'U' ) OR
( o.type = 'TR' ) OR
( o.type = 'V' ) ) AND
( o.is_ms_shipped = 0 ) AND
( o.name LIKE @ObjectName OR @ObjectName IS NULL )
ORDER BY SCHEMA_NAME( o.schema_id ),
CASE o.type WHEN 'U' THEN 1
WHEN 'V' THEN 2
WHEN 'FN' THEN 3
WHEN 'IF' THEN 4
WHEN 'TF' THEN 5
WHEN 'P' THEN 6
WHEN 'TR' THEN 7
ELSE 9 END,
o.name
OPEN name_cursor
FETCH NEXT FROM name_cursor
INTO @schemaname, @id, @name, @Type
DECLARE @C_F INT,
@C_P INT,
@C_TR INT,
@C_V INT
SELECT @C_F = 0,
@C_P = 0,
@C_TR = 0,
@C_V = 0
DECLARE @drop_name VARCHAR(256),
@Is_name VARCHAR(256)
WHILE @@FETCH_STATUS = 0
BEGIN
IF( @Type = 'P' )
BEGIN
SELECT @C_P = @C_P + 1,
@Is_name = 'OBJECTPROPERTY( object_id, N''IsProcedure'') = 1',
@drop_name = 'PROCEDURE'
END
ELSE IF( @type = 'FN' OR @type = 'IF' OR @type = 'TF' )
BEGIN
SELECT @C_F = @C_F + 1,
@Is_name = 'OBJECTPROPERTY( object_id, N''IsTableFunction'') IS NOT NULL',
@drop_name = 'FUNCTION'
END
ELSE IF( @type = 'TR' )
BEGIN
SELECT @C_TR = @C_TR + 1,
@Is_name = 'OBJECTPROPERTY( object_id, N''IsTrigger'') = 1',
@drop_name = 'TRIGGER'
END
ELSE IF( @type = 'V' )
BEGIN
SELECT @C_V = @C_V + 1,
@Is_name = 'OBJECTPROPERTY( object_id, N''IsView'') = 1',
@drop_name = 'VIEW'
END
IF( @Type <> 'U' )
BEGIN
SELECT @schemaname = CASE @schemaname WHEN @default_schema_name THEN '' ELSE '[' + @schemaname + '].' END
PRINT '/*----------------------------------------------------------*/'
PRINT '/* ' + @schemaname + '[' + @name + ']' + space( 54 - 2 - LEN( @name ) ) + '*/'
PRINT '/*----------------------------------------------------------*/'
PRINT 'IF EXISTS ( SELECT 1 FROM sys.objects o WHERE object_id = object_id( N''' + @schemaname + '[' + @name + ']'' ) AND ' + @Is_name + ' )'
PRINT 'DROP ' + @drop_name + ' ' + @schemaname + '[' + @name + ']'
PRINT 'GO'
DECLARE @FirstLine VARCHAR( max ),
@MidLine VARCHAR( max ),
@ENDLine VARCHAR( max )
SELECT @temp = '',
@FirstLine = '',
@MidLine = '',
@ENDLine = ''
DECLARE text_cursor CURSOR FOR
SELECT syscomments.text
FROM syscomments
WHERE ( syscomments.id = @id )
ORDER BY colid
OPEN text_cursor
FETCH NEXT FROM text_cursor INTO @Text
WHILE( @@FETCH_STATUS = 0 )
BEGIN
WHILE( CHARINDEX( ' ' + CHAR(13) + CHAR(10) + 'CREATE PROC', @Text ) > 0 )
BEGIN
SELECT @Text = REPLACE( @Text, ' ' + CHAR(13) + CHAR(10) + 'CREATE PROC', 'CREATE PROC' )
END
WHILE( CHARINDEX( ' ' + CHAR(13) + CHAR(10) + 'CREATE TRIGGER', @Text ) > 0 )
BEGIN
SELECT @Text = REPLACE( @Text, ' ' + CHAR(13) + CHAR(10) + 'CREATE TRIGGER', 'CREATE TRIGGER' )
END
WHILE( CHARINDEX( ' ' + CHAR(13) + CHAR(10) + 'CREATE FUNC', @Text ) > 0 )
BEGIN
SELECT @Text = REPLACE( @Text, ' ' + CHAR(13) + CHAR(10) + 'CREATE FUNC', 'CREATE FUNC' )
END
WHILE( CHARINDEX( ' ' + CHAR(13) + CHAR(10) + 'CREATE VIEW', @Text ) > 0 )
BEGIN
SELECT @Text = REPLACE( @Text, ' ' + CHAR(13) + CHAR(10) + 'CREATE VIEW', 'CREATE VIEW' )
END
SELECT @Text = REPLACE( @Text, CHAR(13), CHAR(10) )
WHILE( CHARINDEX( CHAR(10) + CHAR(10), @Text, 1 ) > 0 )
BEGIN
SELECT @Text = REPLACE( @Text, CHAR(10) + CHAR(10), CHAR(10) )
END
SELECT @pos = 0,
@pos1 = 0
SELECT @pos = CHARINDEX( CHAR(10), @Text, @pos )
IF( @pos > 0 )
BEGIN
SELECT @FirstLine = ISNULL( @ENDLine, '' ) + ISNULL( LEFT( @Text, @pos - 1 ), '' )
SELECT @FirstLine = REPLACE( @FirstLine, CHAR(10), CHAR(13) + CHAR(10) )
WHILE( ASCII( RIGHT( @FirstLine, 1 ) ) < 32 )
BEGIN
SELECT @FirstLine = LEFT( @FirstLine, LEN( @FirstLine + '#' ) - 2 )
END
PRINT @FirstLine
SELECT @Text = SubString( @Text, @pos + 1, 8000 )
SELECT @pos = 0,
@pos1 = 0
SELECT @pos = CHARINDEX( CHAR(10), @Text, @pos )
WHILE ( @pos > 0 )
BEGIN
SELECT @pos1 = @pos
SELECT @pos = CHARINDEX( CHAR(10), @Text, @pos + 1 )
END
IF( @pos1 > 0 )
BEGIN
SELECT @MidLine = LEFT( @Text, @pos1 - 1 )
END
ELSE
BEGIN
SELECT @MidLine = @Text
END
SELECT @MidLine = ISNULL( REPLACE( @MidLine, CHAR(10), CHAR(13) + CHAR(10) ), '' )
WHILE( ASCII( RIGHT( @MidLine, 1 ) ) < 32 )
BEGIN
SELECT @MidLine = LEFT( @MidLine, LEN( @MidLine + '#' ) - 2 )
END
PRINT @MidLine
IF( @pos1 > 0 )
BEGIN
SELECT @ENDLine = SubString( @Text, @pos1 + 1, 8000 )
END
ELSE
BEGIN
SELECT @ENDLine = ''
END
END
ELSE
BEGIN
SELECT @ENDLine = ISNULL( @ENDLine, '' ) + ISNULL( @Text, '' )
END
FETCH NEXT FROM text_cursor INTO @Text
END
CLOSE text_cursor
DEALLOCATE text_cursor
WHILE( ASCII( RIGHT( @ENDLine, 1 ) ) < 32 )
BEGIN
SELECT @ENDLine = LEFT( @ENDLine, LEN( @ENDLine + '#' ) - 2 )
END
SELECT @ENDLine = REPLACE( @ENDLine, CHAR(10), CHAR(13) + CHAR(10) )
IF( @ENDLine IS NOT NULL AND @ENDLine <> '' )
BEGIN
PRINT @ENDLine
END
PRINT 'GO'
END
FETCH NEXT FROM name_cursor
INTO @schemaname, @id, @name, @type
END
CLOSE name_cursor
DEALLOCATE name_cursor
SELECT @C_F AS [FUNC],
@C_P AS [PROC],
@C_TR AS [TRIG],
@C_V AS [VIEW]
SELECT DISTINCT schema_name( o.schema_id ),
o.object_id,
o.name,
o.type,
o.create_date,
o.modify_date,
CASE o.type WHEN 'U' THEN 1
WHEN 'V' THEN 2
WHEN 'FN' THEN 3
WHEN 'IF' THEN 4
WHEN 'TF' THEN 5
WHEN 'P' THEN 6
WHEN 'TR' THEN 7 END as ListOrder
FROM sys.all_objects o
WHERE ( ( o.type = 'P' ) OR
( o.type = 'FN' ) OR
( o.type = 'IF' ) OR
( o.type = 'TF' ) OR
( o.type = 'TR' ) OR
( o.type = 'V' ) ) AND
( o.is_ms_shipped = 0 ) AND
( o.name LIKE @ObjectName OR @ObjectName IS NULL )
ORDER BY schema_name( o.schema_id ),
CASE o.type WHEN 'U' THEN 1
WHEN 'V' THEN 2
WHEN 'FN' THEN 3
WHEN 'IF' THEN 4
WHEN 'TF' THEN 5
WHEN 'P' THEN 6
WHEN 'TR' THEN 7 END,
o.name
SET NOCOUNT OFF
END
GO