几个有用的T-SQL(1)

--Disable Constraints & Triggers  

EXEC sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' 

EXEC sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL' 

--Perform delete operation on all table for cleanup  

EXEC sp_MSforeachtable 'DELETE ?' 

--Enable Constraints & Triggers again  

EXEC sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL' 

EXEC sp_MSforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL' 

--Reset Identity on tables with identity column  

EXEC sp_MSforeachtable 'IF OBJECTPROPERTY(OBJECT_ID(''?''), ''TableHasIdentity'') = 1  

BEGIN DBCC CHECKIDENT (''?'',RESEED,0) END' 

CREATE PROCEDURE [dbo].[GetDBTableSize]  

AS 

BEGIN 

SET NOCOUNT ON;  

DECLARE @cmdstr varchar(100)  

--Create Temporary Table  

CREATE TABLE #TempTable  

(  

    [Table_Name] varchar(50),  

    Row_Count int,  

    Table_Size varchar(50),  

    Data_Space_Used varchar(50),  

    Index_Space_Used varchar(50),  

    Unused_Space varchar(50)  

)  

--Create Stored Procedure String  

SELECT @cmdstr = 'sp_msforeachtable ''sp_spaceused "?"''' 

--Populate Tempoary Table  

INSERT INTO #TempTable EXEC(@cmdstr)  

--Determine sorting method  

SELECT * FROM #TempTable ORDER BY Table_Name  

--Delete Temporay Table  

DROP TABLE #TempTable  

END 

BACKUP log [myDatabase] with truncate_only  

go  

DBCC SHRINKDATABASE ([myDatabase], 10, TRUNCATEONLY)  

go 

BACKUP  

LOG [myDatabase] TO DISK = N'C:\Backups\myDatabase_log.trn' WITH 

NOFORMAT, NOINIT, NAME = N'myDatabase_log', SKIP, REWIND, NOUNLOAD,  

STATS = 10 

USE YOURDBNAME  

SELECT COUNT(*) from information_schema.tables  

WHERE table_type = 'base table' 

DECLARE @currentday varchar(10)  

set @currentday = datepart(day,getdate())  

IF LEN(@currentday) = 1  

BEGIN 

    SET @currentday = '0' + @currentday  

END 

DECLARE @currentmonth varchar(10)  

SET @currentmOnth= datepart(month,getdate())  

IF LEN(@currentmonth) = 1  

BEGIN 

    SET @currentmOnth= '0' + @currentmonth  

END 

DECLARE @currentyear varchar(10)  

SET @currentyear = datepart(year,getdate())  

DECLARE @fileName varchar(100)  

SET @fileName = 'D:\dbbackup\' + @currentyear  

+ '_'    + @currentmonth  + '_' + @currentday  + '.bak' 

BACKUP DATABASE yourdatabase TO DISK = @fileName WITH NOFORMAT, INIT,  

NAME = N'myDatabase -Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10  

GO