批处理建库（续）

--创建触发器 视图 存储过程  函数

Create PROCEDURE [dbo].[CreateProcfunction](@basename varchar(30))
AS
declare @sql varchar(1000)

BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

    -- Insert statements for procedure here
 set @sql = 'SELECT a.[text] from '+@basename+'..syscomments a,'+@basename+'..Sysobjects b '
               +'where b.xtype in (''P'',''FN'',''TR'',''V'') and a.id=b.id'
 exec(@sql)
END

 

--删除库中表的所有内容

ALTER PROCEDURE [dbo].[DeleteTableName]( @basename varchar(30))
 -- Add the parameters for the stored procedure here

AS
declare @sql varchar(1000)
BEGIN
 
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.

    set @sql = 'select ''delete from ''+'+ 'u.[name]+''.''+''[''+o.[name]+'']'' from '+@basename+'..sysobjects o
                inner join '+@basename+'..sysusers u on o.uid = u.uid where o.xtype= ''u'' order by o.[name]'
    exec (@sql)

END

 

 

@if {%1}=={}  goto :usagename
@if {%2}=={}  goto :usagepath
@sqlcmd -S tcp:172.16.3.53/ZHANGXZ-4B7215C,1433 -U xep -P xep -d attc -h-1 -q " exec dbo.impSelectAllTableName '%1' " -o "c:/imp1.bat"
@call c:/imp1.bat
@goto :end
:usagename
@echo 请输入数据库名称!
:usagepath
@echo 请输入存放文件的路径!
:end
@echo 导入完成!

 

说明 %1 是批处理接收的第一个参数 %2是第二个  依此类推

       -U   是登录库的用户 -P 密码  -d 连接库名  

       -h-1是去掉查询出来的表头

       -q   是使用的sql语句

       -o   是输出到结果文件