CREATE PROC dbo.usp_OutObjects
@dbname sysname,
@Path NVARCHAR(1024)
AS
BEGIN
DECLARE
@viewPath NVARCHAR(1024),
@functionPath NVARCHAR(1024),
@procedurePath NVARCHAR(1024),
@triggerPath NVARCHAR(1024);
SELECT
@viewPath=@Path+'Views/',
@functionPath=@Path+'Functions/',
@procedurePath=@Path+'Procedures/',
@triggerPath=@Path+'Triggers/';
DECLARE @cmd NVARCHAR(4000);
SET @cmd='md "'+@viewPath+'"';
EXEC master.dbo.xp_cmdshell @cmd,no_output;
SET @cmd='md "'+@functionPath+'"';
EXEC master.dbo.xp_cmdshell @cmd,no_output;
SET @cmd='md "'+@procedurePath+'"';
EXEC master.dbo.xp_cmdshell @cmd,no_output;
SET @cmd='md "'+@triggerPath+'"';
EXEC master.dbo.xp_cmdshell @cmd,no_output;
IF DB_ID(@dbname) IS NULL OR ISNULL(NULLIF(@dbname,''),'')=''
SET @dbname=db_name();
CREATE TABLE MyTest..OutputObjects
(
[object_id] INT,
[name] sysname,
[text] NVARCHAR(MAX),
[type] TINYINT -- 0:Views ,1:function ,2:procedure ,3:trigger
);
SET @cmd=N'USE ['+@dbname+']';
SET @cmd=@cmd+CHAR(13)+CHAR(10);
SET @cmd=@cmd+'INSERT INTO MyTest..OutputObjects
SELECT
o.[object_id],
o.name,
m.definition,
CASE WHEN OBJECTPROPERTY(o.[object_id],''IsView'')=1
THEN 0
WHEN OBJECTPROPERTY(o.[object_id],''IsScalarFunction'')=1
OR OBJECTPROPERTY(o.[object_id],''IsTableFunction'')=1
THEN 1
WHEN OBJECTPROPERTY(o.[object_id],''IsProcedure'')=1
THEN 2
WHEN OBJECTPROPERTY(o.[object_id],''IsTrigger'')=1
THEN 3
END
FROM sys.objects AS o
JOIN sys.sql_modules AS m
ON o.[object_id]=m.[object_id]
WHERE OBJECTPROPERTY(o.[object_id],''IsEncrypted'')=0
AND OBJECTPROPERTY(o.[object_id],''IsExecuted'')=1
AND o.is_ms_shipped=0
ORDER BY o.[object_id];';
EXEC(@cmd);
DECLARE @object_id INT;
DECLARE @filename NVARCHAR(2056);
SET @object_id=(SELECT MIN([object_id]) FROM MyTest..OutputObjects);
WHILE @object_id IS NOT NULL
BEGIN
SELECT
@filename=
CASE [type]
WHEN 0 THEN @viewPath
WHEN 1 THEN @functionPath
WHEN 2 THEN @procedurePath
WHEN 3 THEN @triggerpath
END +name + '.sql'
FROM MyTest.dbo.OutputObjects
WHERE [object_id]=@object_id;
SET @cmd=N'bcp "SELECT [text] FROM MyTest.dbo.OutputObjects';
SET @cmd=@cmd+N' WHERE [object_id]='+RTRIM(@object_id)
SET @cmd=@cmd+N'" queryout "'+@filename+'"'
SET @cmd=@cmd+N' -q -w -T -Smyfend/LIANGCK';
EXEC master.dbo.xp_cmdshell @cmd,no_output;
SET @object_id=(SELECT MIN([object_id]) FROM MyTest.dbo.OutputObjects
WHERE [object_id]>@object_id);
END
DROP TABLE MyTest..OutputObjects;
END
GO
EXEC dbo.usp_OutObjects 'MyTest','G:/Test/'
GO
DROP PROC dbo.usp_OutObjects
@dbname sysname,
@Path NVARCHAR(1024)
AS
BEGIN
DECLARE
@viewPath NVARCHAR(1024),
@functionPath NVARCHAR(1024),
@procedurePath NVARCHAR(1024),
@triggerPath NVARCHAR(1024);
SELECT
@viewPath=@Path+'Views/',
@functionPath=@Path+'Functions/',
@procedurePath=@Path+'Procedures/',
@triggerPath=@Path+'Triggers/';
DECLARE @cmd NVARCHAR(4000);
SET @cmd='md "'+@viewPath+'"';
EXEC master.dbo.xp_cmdshell @cmd,no_output;
SET @cmd='md "'+@functionPath+'"';
EXEC master.dbo.xp_cmdshell @cmd,no_output;
SET @cmd='md "'+@procedurePath+'"';
EXEC master.dbo.xp_cmdshell @cmd,no_output;
SET @cmd='md "'+@triggerPath+'"';
EXEC master.dbo.xp_cmdshell @cmd,no_output;
IF DB_ID(@dbname) IS NULL OR ISNULL(NULLIF(@dbname,''),'')=''
SET @dbname=db_name();
CREATE TABLE MyTest..OutputObjects
(
[object_id] INT,
[name] sysname,
[text] NVARCHAR(MAX),
[type] TINYINT -- 0:Views ,1:function ,2:procedure ,3:trigger
);
SET @cmd=N'USE ['+@dbname+']';
SET @cmd=@cmd+CHAR(13)+CHAR(10);
SET @cmd=@cmd+'INSERT INTO MyTest..OutputObjects
SELECT
o.[object_id],
o.name,
m.definition,
CASE WHEN OBJECTPROPERTY(o.[object_id],''IsView'')=1
THEN 0
WHEN OBJECTPROPERTY(o.[object_id],''IsScalarFunction'')=1
OR OBJECTPROPERTY(o.[object_id],''IsTableFunction'')=1
THEN 1
WHEN OBJECTPROPERTY(o.[object_id],''IsProcedure'')=1
THEN 2
WHEN OBJECTPROPERTY(o.[object_id],''IsTrigger'')=1
THEN 3
END
FROM sys.objects AS o
JOIN sys.sql_modules AS m
ON o.[object_id]=m.[object_id]
WHERE OBJECTPROPERTY(o.[object_id],''IsEncrypted'')=0
AND OBJECTPROPERTY(o.[object_id],''IsExecuted'')=1
AND o.is_ms_shipped=0
ORDER BY o.[object_id];';
EXEC(@cmd);
DECLARE @object_id INT;
DECLARE @filename NVARCHAR(2056);
SET @object_id=(SELECT MIN([object_id]) FROM MyTest..OutputObjects);
WHILE @object_id IS NOT NULL
BEGIN
SELECT
@filename=
CASE [type]
WHEN 0 THEN @viewPath
WHEN 1 THEN @functionPath
WHEN 2 THEN @procedurePath
WHEN 3 THEN @triggerpath
END +name + '.sql'
FROM MyTest.dbo.OutputObjects
WHERE [object_id]=@object_id;
SET @cmd=N'bcp "SELECT [text] FROM MyTest.dbo.OutputObjects';
SET @cmd=@cmd+N' WHERE [object_id]='+RTRIM(@object_id)
SET @cmd=@cmd+N'" queryout "'+@filename+'"'
SET @cmd=@cmd+N' -q -w -T -Smyfend/LIANGCK';
EXEC master.dbo.xp_cmdshell @cmd,no_output;
SET @object_id=(SELECT MIN([object_id]) FROM MyTest.dbo.OutputObjects
WHERE [object_id]>@object_id);
END
DROP TABLE MyTest..OutputObjects;
END
GO
EXEC dbo.usp_OutObjects 'MyTest','G:/Test/'
GO
DROP PROC dbo.usp_OutObjects