create procedure sp_cycle_errorlog --- 1997/06/24
as
if (not (is_srvrolemember('sysadmin') = 1)) -- Make sure that it is the SA executing this.
begin
raiserror(15247,-1,-1)
return(1)
end
dbcc errorlog
return (0)
GO

獲取數(shù)據(jù)庫中所有的表
SELECT SysObjects.name AS Tablename FROM sysobjects WHERE xtype = 'U' and sysstat200
獲取數(shù)據(jù)庫中所有表的列名
SELECT SysColumns.name AS Columnsname, SysObjects.name AS Tablename FROM SysObjects, SysColumns WHERE Sysobjects.Xtype='u' AND Sysobjects.Id=Syscolumns.Id
獲取SQL所有數(shù)據(jù)庫名、所有表名、所有字段名、表字段長度
1.獲取MSSQL中的所有數(shù)據(jù)庫名:
SELECT name FROM MASter..SysDatabASes ORDER BY name
2.獲取MSSQL中的所有用戶表名:
SELECT name FROM DatabASename..SysObjects WHERE XType='U' ORDER BY name
XType='U':表示所有用戶表;
XType='S':表示所有系統(tǒng)表;
3.獲取指定表[tb_phone]的所有字段名:
SELECT name FROM SysColumns WHERE id=Object_Id('tb_phone')
4.SQL所有表的表名、所有字段名、表字段長度
SELECT table_name AS 數(shù)據(jù)表名,
column_name AS 字段名,
ISNULL(column_default,'') AS 默認(rèn)值,
is_nullable AS 是否允許為NULL,
data_type AS 數(shù)據(jù)類型,
ISNULL(ISNULL(ISNULL(character_maximum_length,numeric_precision),datetime_precision),1) AS 類型長度
FROM information_schema.columns
WHERE NOT table_name IN('sysdiagrams','dtproperties')
5.獲取指定表[tb_phone]的表名，表字段名，字段類型和類型長度
SELECT SysObjects.name AS Tablename,
Syscolumns.name AS Columnsname,
Systypes.name AS DateType,
Syscolumns.length AS DateLength
FROM Sysproperties RIGHT OUTER JOIN
Sysobjects INNER JOIN
Syscolumns ON Sysobjects.id = Syscolumns.id INNER JOIN
Systypes ON Syscolumns.xtype = Systypes.xtype ON
Sysproperties.id = Syscolumns.id AND
Sysproperties.smallid = Syscolumns.colid
WHERE (Sysobjects.xtype = 'u' OR
Sysobjects.xtype = 'v') AND (Systypes.name > 'Sysname') AND
(Sysobjects.name = 'tb_phone')
ORDER BY Columnsname

EXEC sp_detach_db @dbname = '77169database'
EXEC sp_attach_single_file_db @dbname = '77169database',
@physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\77169database.mdf'

DUMP TRANSACTION 庫名 WITH NO_LOG

USE MASTER
GO
ALTER DATABASE 數(shù)據(jù)庫名 SET RECOVERY SIMPLE
GO
------------------------------------------
截?cái)嗍聞?wù)日志：
BACKUP LOG { database_name | @database_name_var }
{
[ WITH
{ NO_LOG | TRUNCATE_ONLY } ]
}
--壓縮日志及數(shù)據(jù)庫文件大小
/*--特別注意
請(qǐng)按步驟進(jìn)行,未進(jìn)行前面的步驟,請(qǐng)不要做后面的步驟，否則可能損壞你的數(shù)據(jù)庫.
--*/

alter database 數(shù)據(jù)庫名 modify file(name=邏輯文件名,maxsize=20)
--------------------------------------------------------------
/*--壓縮數(shù)據(jù)庫的通用存儲(chǔ)過程
壓縮日志及數(shù)據(jù)庫文件大小，因?yàn)橐獙?duì)數(shù)據(jù)庫進(jìn)行分離處理，所以存儲(chǔ)過程不能創(chuàng)建在被壓縮的數(shù)據(jù)庫中。
/*--調(diào)用示例
exec p_compdb 'test'
--*/
use master --注意,此存儲(chǔ)過程要建在master數(shù)據(jù)庫中
go
if exists (select * from dbo.sysobjects where id
= object_id(N'[dbo].[p_compdb]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_compdb]
GO
create proc p_compdb
@dbname sysname, --要壓縮的數(shù)據(jù)庫名
@bkdatabase bit=1, --因?yàn)榉蛛x日志的步驟中,可能會(huì)損壞數(shù)據(jù)庫,所以你可以選擇是否自動(dòng)數(shù)據(jù)庫
@bkfname nvarchar(260)='' --備份的文件名,如果不指定,自動(dòng)備份到默認(rèn)備份目錄,
備份文件名為:數(shù)據(jù)庫名+日期時(shí)間
as

if @bkdatabase=1
begin
if isnull(@bkfname,'')=''
set @bkfname=@dbname+'_'+convert(varchar,getdate(),112)
+replace(convert(varchar,getdate(),108),':','')
select 提示信息='備份數(shù)據(jù)庫到SQL 默認(rèn)備份目錄,備份文件名:'+@bkfname
exec('backup database ['+@dbname+'] to disk='''+@bkfname+'''')
end
--進(jìn)行分離處理
create table #t(fname nvarchar(260),type int)
exec('insert into #t select filename,type=status0x40 from ['+@dbname+']..sysfiles')
exec('sp_detach_db '''+@dbname+'''')
--刪除日志文件
declare @fname nvarchar(260),@s varchar(8000)
declare tb cursor local for select fname from #t where type=64
pen tb
fetch next from tb into @fname
while @@fetch_status=0
begin
set @s='del "'+rtrim(@fname)+'"'
exec master..xp_cmdshell @s,no_output
fetch next from tb into @fname
end
close tb
deallocate tb
--附加數(shù)據(jù)庫
set @s=''
declare tb cursor local for select fname from #t where type=0
open tb
fetch next from tb into @fname
while @@fetch_status=0
begin
set @s=@s+','''+rtrim(@fname)+''''
fetch next from tb into @fname
end
close tb
deallocate tb
exec('sp_attach_single_file_db '''+@dbname+''''+@s)
go