记几个自己的存储过程
创建文件夹
USE []GO/****** 对象: StoredProcedure [dbo].[createFolders] 脚本日期: 06/06/2012 14:31:06 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER Proc [dbo].[createFolders] As Declare @id nvarchar(20) Declare @name nvarchar(20) Declare @foldername nvarchar(40) Declare @MasterGoodsCursor Cursor Exec getFolderName @MasterGoodsCursor out Fetch Next From @MasterGoodsCursor InTo @id,@name While(@@Fetch_Status = 0) Begin Begin set @foldername='md D:\car_data\'+@id+'_'+@name /* Print @flodername */ exec master..xp_cmdshell @foldername End Fetch Next From @MasterGoodsCursor InTo @id,@name End Close @MasterGoodsCursor Deallocate @MasterGoodsCursor
导出txt格式的表数据
??USE []GO/****** 对象: StoredProcedure [dbo].[ExpAllCarData] 脚本日期: 06/06/2012 14:32:27 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER Proc [dbo].[ExpAllCarData] As Declare @TablesName nvarchar(100) Declare @info nvarchar(500) Declare @name nvarchar(100) Declare @getTableName Cursor Exec getTableName @getTableName out Fetch Next From @getTableName InTo @TablesName While(@@Fetch_Status = 0) Begin Begin set @name=@TablesNameset @info='bcp "diagnosis_changan.dbo.'+@name+'" out "d:\'+@name+'.txt" -c -q -S. -U"sa" -P"。。。"' /* */ EXEC master..xp_cmdshell @info /* Print @info */ End Fetch Next From @getTableName InTo @TablesName End Close @getTableName Deallocate @getTableName?获取数据库表空间GO/****** 对象: StoredProcedure [dbo].[GetAllTableSizes] 脚本日期: 06/06/2012 14:33:46 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[GetAllTableSizes]ASDECLARE @TableName VARCHAR(100)DECLARE tableCursor CURSOR FORWARD_ONLYFORselect [name] from dbo.sysobjects where OBJECTPROPERTY(id, N'IsUserTable') = 1 FOR READ ONLY CREATE TABLE #TempTable( tableName varchar(100), numberofRows varchar(100), reservedSize varchar(50), dataSize varchar(50), indexSize varchar(50), unusedSize varchar(50))OPEN tableCursorWHILE (1=1)BEGIN FETCH NEXT FROM tableCursor INTO @TableName IF(@@FETCH_STATUS<>0) BREAK; INSERT #TempTable EXEC sp_spaceused @TableNameENDCLOSE tableCursorDEALLOCATE tableCursor UPDATE #TempTableSET reservedSize = REPLACE(reservedSize, ' KB', '') SELECT tableName 'Table Name', numberofRows 'Total Rows', reservedSize 'Reserved KB', dataSize 'Data Size', indexSize 'Index Size', unusedSize 'Unused Size'FROM #TempTableORDER BY CONVERT(bigint,reservedSize) DESC DROP TABLE #TempTable??
?
