关于 SQL Server ErrorLog 错误日志说明

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

获取数据库中所有的表
SELECT SysObjects.name AS Tablename FROM sysobjects WHERE xtype = U and sysstat<200
获取数据库中所有表的列名
SELECT SysColumns.name AS Columnsname, SysObjects.name AS Tablename FROM SysObjects, SysColumns WHERE Sysobjects.Xtype=u AND Sysobjects.Id=Syscolumns.Id
获取SQL所有数据库名、所有表名、所有字段名、表字段长度
1.获取MSSQL中的所有数据库名:
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:表示所有系统表;
3.获取指定表[tb_phone]的所有字段名:
SELECT name FROM SysColumns WHERE id=Object_Id(tb_phone)
4.SQL所有表的表名、所有字段名、表字段长度
SELECT table_name AS 数据表名,
column_name AS 字段名,
ISNULL(column_default,) AS 默认值,
is_nullable AS 是否允许为NULL,
data_type AS 数据类型,
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 数据库名 SET RECOVERY SIMPLE
GO
------------------------------------------
截断事务日志：
BACKUP LOG { database_name | @database_name_var }
{
[ WITH
{ NO_LOG | TRUNCATE_ONLY } ]
}
--压缩日志及数据库文件大小
/*--特别注意
请按步骤进行,未进行前面的步骤,请不要做后面的步骤，否则可能损坏你的数据库.
--*/

alter database 数据库名 modify file(name=逻辑文件名,maxsize=20)
--------------------------------------------------------------
/*--压缩数据库的通用存储过程
压缩日志及数据库文件大小，因为要对数据库进行分离处理，所以存储过程不能创建在被压缩的数据库中 。
/*--调用示例
exec p_compdb test
--*/
use master --注意,此存储过程要建在master数据库中
go
if exists (select * from dbo.sysobjects where id
= object_id(N[dbo].[p_compdb]) and OBJECTPROPERTY(id, NIsProcedure) = 1)
drop procedure [dbo].[p_compdb]
GO
create proc p_compdb
@dbname sysname, --要压缩的数据库名
@bkdatabase bit=1, --因为分离日志的步骤中,可能会损坏数据库,所以你可以选择是否自动数据库
@bkfname nvarchar(260)= --备份的文件名,如果不指定,自动备份到默认备份目录,
备份文件名为:数据库名+日期时间
as

if @bkdatabase=1
begin
if isnull(@bkfname,)=
set @bkfname=@dbname+_+convert(varchar,getdate(),112)
+replace(convert(varchar,getdate(),108),:,)
select 提示信息=备份数据库到SQL 默认备份目录,备份文件名:+@bkfname
exec(backup database [+@dbname+] to disk=+@bkfname+)
end
--进行分离处理
create table #t(fname nvarchar(260),type int)
exec(insert into #t select filename,type=status&0x40 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
--附加数据库
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