创建文件夹
USE []
GO
/****** 对象: StoredProcedure [dbo].[createFolders] 脚本日期: 06/06/2012 14:31:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER 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 ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER 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=@TablesName
set @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
获取数据库表空间
USE []
GO
/****** 对象: StoredProcedure [dbo].[GetAllTableSizes] 脚本日期: 06/06/2012 14:33:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetAllTableSizes]
AS
DECLARE @TableName VARCHAR(100)
DECLARE tableCursor CURSOR FORWARD_ONLY
FOR
select [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 tableCursor
WHILE (1=1)
BEGIN
FETCH NEXT FROM tableCursor INTO @TableName
IF(@@FETCH_STATUS<>0) BREAK;
INSERT #TempTable EXEC sp_spaceused @TableName
END
CLOSE tableCursor
DEALLOCATE tableCursor
UPDATE #TempTable
SET reservedSize = REPLACE(reservedSize, ' KB', '')
SELECT tableName 'Table Name',
numberofRows 'Total Rows',
reservedSize 'Reserved KB',
dataSize 'Data Size',
indexSize 'Index Size',
unusedSize 'Unused Size'
FROM #TempTable
ORDER BY CONVERT(bigint,reservedSize) DESC
DROP TABLE #TempTable