导出数据库中所有的对象到指定的目录bcp xp_cmdshell

最新推荐文章于 2024-09-14 19:55:29 发布

weixin_30807779

最新推荐文章于 2024-09-14 19:55:29 发布

阅读量75

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/mingyongcheng/archive/2012/06/14/2549471.html

版权

set rowcount 1
go

DECLARE CustObject_Cursor CURSOR
KEYSET
FOR

select name,rtrim(LTRIM(Type)) from sysobjects where xtype<>'S'
DECLARE @ObjectName nvarchar(max)
DECLARE @Type nvarchar(50)
OPEN CustObject_Cursor
FETCH NEXT FROM CustObject_Cursor INTO @ObjectName ,@Type
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN

select top 1 text from syscomments where id=OBJECT_ID(@ObjectName)

declare @cmd varchar(8000)

declare @path nvarchar(500)
set @path = 'G:\me_doc\'+DB_NAME()+'\'+@Type+'\'

declare @cmdCreateDir varchar(500)
set @cmdCreateDir='if not exist '+@path+' md '+@path+''

set @cmd = 'bcp "select top 1 text from '+DB_NAME()+'.sys.syscomments where id='+cast(OBJECT_ID(@ObjectName)as nvarchar)+' " queryout '+@path+@ObjectName+'.sql -c -T'
print @cmd

exec master.dbo.xp_cmdShell @cmdCreateDir
exec master.dbo.xp_cmdShell @cmd

END
FETCH NEXT FROM CustObject_Cursor INTO @ObjectName ,@Type
END
CLOSE CustObject_Cursor
DEALLOCATE CustObject_Cursor

转载于:https://www.cnblogs.com/mingyongcheng/archive/2012/06/14/2549471.html