46 lines
2.4 KiB
Transact-SQL
46 lines
2.4 KiB
Transact-SQL
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp__GenerateSQLScript]') AND type in (N'P', N'PC'))
|
||
DROP PROCEDURE [dbo].[sp__GenerateSQLScript]
|
||
GO
|
||
SET ANSI_NULLS ON
|
||
GO
|
||
SET QUOTED_IDENTIFIER ON
|
||
GO
|
||
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp__GenerateSQLScript]') AND type in (N'P', N'PC'))
|
||
BEGIN
|
||
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[sp__GenerateSQLScript] AS'
|
||
END
|
||
GO
|
||
ALTER PROCEDURE [dbo].[sp__GenerateSQLScript]
|
||
AS
|
||
BEGIN
|
||
|
||
declare @tSysObjects table (
|
||
objectName varchar(255),
|
||
objectId int,
|
||
Processed bit default(0) )
|
||
|
||
insert into @tSysObjects
|
||
select name, object_id, 0 from sys.objects where type = 'FN' or type = 'P' order by 1
|
||
|
||
declare @allObjects nvarchar(max)
|
||
set @allObjects = space(0)
|
||
|
||
while (Select Count(*) From @tSysObjects Where Processed = 0) > 0
|
||
Begin
|
||
declare @objectId int
|
||
select top 1 @objectId = objectId from @tSysObjects where Processed = 0
|
||
declare @oneObject nvarchar(max)
|
||
set @oneObject = space(0)
|
||
select object_definition(@objectId)
|
||
|
||
--set @allObjects = @allObjects + @oneObject
|
||
update @tSysObjects set Processed = 1 where objectId = @objectId
|
||
end
|
||
END
|
||
|
||
|
||
|
||
|
||
|
||
GO
|