一些常用的SQL(05版以上)数据库维护脚本

最新推荐文章于 2024-07-23 17:52:34 发布

crp10018

最新推荐文章于 2024-07-23 17:52:34 发布

阅读量142

点赞数

文章标签：数据库 php

---try
sysaltfiles 主数据库保存数据库的文件
syscharsets 主数据库字符集与排序顺序
sysconfigures 主数据库配置选项
syscurconfigs 主数据库当前配置选项
sysdatabases 主数据库服务器中的数据库
syslanguages 主数据库语言
syslogins 主数据库登陆帐号信息
sysoledbusers 主数据库链接服务器登陆信息
sysprocesses 主数据库进程
sysremotelogins 主数据库远程登录帐号
syscolumns 每个数据库列
sysconstrains 每个数据库限制
sysfilegroups 每个数据库文件组
sysfiles 每个数据库文件
sysforeignkeys 每个数据库外部关键字
sysindexs 每个数据库索引
sysmembers 每个数据库角色成员
sysobjects 每个数据库所有数据库对象
syspermissions 每个数据库权限
systypes 每个数据库用户定义数据类型
sysusers 每个数据库用户
/**//*********************************************************************************
* FielName : backup.sql
* Function : 自动备份
* Author : Yahong
* Date : 2005-5-10 2005-5-19 2006-8-1 2007-09-18
* Version : 00 01 02 03
*
* Remark :
* 2006-08-01 增加差异备份和完全备份两种情况,生成多个备份副本
* 2008-09-18 增加备份一个实例中的所有数据库的情况,并在备份后清除日志
*
*********************************************************************************/
use master
declare @DbName varchar(255),@dir varchar(256),@dir_db varchar(256),
@verb varchar(256),@cmd varchar(256),
@backup_name varchar(256),@dynamic_name varchar(10),
@disk_name varchar(256),@copy nvarchar(100),
@today datetime,@weekday int
--建立网络连接
exec xp_cmdshell 'net use K: /delete'
exec xp_cmdshell 'net use I: /delete'

exec xp_cmdshell 'net use K: \\193.254.40.118\backup backup /user:Web\backup '
exec xp_cmdshell 'net use I: \\172.16.8.48\databackup backup /user:QA-SERVER-TEST\backup'
--设定名字
set @today=getdate()
set @dynamic_name=convert(varchar(10),@today,120)
set @dir='K:\'+@dynamic_name
set @dir_db=@dir+'Database'
set @verb='mkdir '
--建立目录
set @cmd=@verb+@dir_db
exec xp_cmdshell @cmd
declare cur_database cursor forward_only read_only for
select name from sysdatabases
where dbid>4 --系统数据库的dbid<=4
open cur_database
fetch next from cur_database
into @DbName

while @@fetch_status=0
begin
set @backup_name= @DbName+'_'+@dynamic_name
set @disk_name=@dir_db+'\'+@backup_name+'.bak'

--添加备份设备
EXEC sp_addumpdevice 'disk',@backup_name, @disk_name

set @weekday= datepart(dw,@today)
if (@weekday=6) --如果是周五,则进行完全备份
BACKUP DATABASE @DbName TO @backup_name
else --其他时候进行差异备份
BACKUP DATABASE @DbName TO @backup_name with differential
--清理日志
backup log @DbName with no_log
--释放设备
exec sp_dropdevice @backup_name
--复制备份副本到其他地方
set @copy='copy '+@disk_name+' I:'
exec xp_cmdshell @copy

--备份下一个数据库
fetch next from cur_database
into @DbName
end
close cur_database
deallocate cur_database
--删除网络连接
exec xp_cmdshell 'net use K: /delete'
exec xp_cmdshell 'net use I: /delete'
/**//******************************************************************************
*
* File Name : Restore.sql
* Function : 数据库还原
* Author : Yahong
* Version : 00
* Date : 2007-09-18
* Remark :
*
*******************************************************************************/
use master
declare
@DbName varchar(255) --数据库的名字
,@WholeFileName varchar(255) --完全备份的文件名
,@DifferentFileName varchar(255) --差异备份的文件名
,@MasterFileName varchar(255) --数据文件名,注意他们都是逻辑名称
,@LogFileName varchar(255) --日志文件名
,@TargetDir varchar(255) --还原后数据库文件所在的路径，如果没有指定该参数，
--则必须存在与原数据库相同的路径
declare @WholeDeviceName varchar(255)
,@DifferenctDeviceName varchar(255)
,@TargetMasterFileName varchar(255)
,@TargetLogFileName varchar(255)
--建立网络链接
exec xp_cmdshell 'net use K: \\172.16.8.48\200709 backup /User:qa-server-test\backup'
--在这里设置需要备份的文件等信息
set @DbName='CCTQA' --需要还原的数据库的名字，注意不要搞错了，否则
--覆盖了其他的数据库，可别说我没有提醒你
set @WholeFileName='CCTQA_2007-09-14.bak' --完全备份文件

--以下4行如果没有，不要指定,把他们注释掉就行了
set @DifferentFileName='CCTQA_2007-09-17.bak' --最后一次差异备份文件
set @MasterFileName='CCTQA_Data' --数据文件
set @LogFileName='CCTQA_Log' --日志文件
set @TargetDir='D:\CCTQA\Databae' --目标路径
--设置目标路径
set @TargetMasterFileName=@TargetDir+'\'+@MasterFileName
set @TargetLogFileName=@TargetDir+'\'+@LogFileName
--添加还原设备
set @WholeDeviceName=@DbName+'WholeDevice'
set @WholeFileName='K:\'+@WholeFileName
exec sp_addumpdevice 'disk',@WholeDeviceName,@WholeFileName
--开始备份
if(isnull(@DifferentFileName,'')<>'') --如果具有差异备份的还原
begin
--添加差异备份还原的设备
set @DifferenctDeviceName=@DbName+'DifferenctDevice'
set @DifferentFileName='K:\'+@DifferentFileName
exec sp_addumpdevice 'disk',@DifferenctDeviceName,@DifferentFileName

--备份
if(isnull(@TargetDir,'')='')
restore database @DbName from @WholeDeviceName
with NORECOVERY
else --如果还原后的数据库文件的路径与备份前的路径不一致
restore database @DbName from @WholeDeviceName
with NORECOVERY,
move @MasterFileName to @TargetMasterFileName,
move @LogFileName to @TargetLogFileName
restore database @DbName from @DifferenctDeviceName
end
else
begin --只有完全备份的还原
if(isnull(@TargetDir,'')='')
restore database @DbName from @WholeFileName
else
restore database @DbName from @WholeFileName
with move @MasterFileName to @TargetMasterFileName,
move @LogFileName to @TargetLogFileName
end
--释放备份设备
exec sp_dropdevice @WholeDeviceName
if(isnull(@DifferentFileName,'')<>'')
exec sp_dropdevice @DifferenctDeviceName
--删除网络链接
exec xp_cmdshell 'net use K: /delete'
/**//************************************************************************
*
* File Name : ShrinkLog.sql
* Function : 收缩数据库的日志文件
* Author ：Yahong
* Version : 00
* Date : 2007-09-16
* Remark :
*
*************************************************************************
--
--第一步：设置需要收缩的数据库，找到需要收缩数据文件
--
use cctqa
select Size/128 Size,Name from sysfiles
/**//*
declare @LogName varchar(255),@TargetSize int
--
--
--第二步：设置需要收缩的日志文件的逻辑名字和收缩后的大小
--千万不要搞错了，选错了文件，有可能会丢失数据，那时候
--哭都哭不回来了。
--
--
set @LogName='CCTQA_Log'
set @TargetSize=1
declare @str varchar(300), @DatabaseName varchar(255)
set @DatabaseName=db_name()
if(not exists(select * from sysfiles where name=@LogName))
begin
set @str='没有找到日志文件'+@LogName
raiserror(@str,0,1)
end else
begin
declare @curSize int,@maxTime int
set @maxTime=10
set @curSize=(select size from sysfiles where name=@LogName)/128
print '收缩之前的日志文件的大小是：'+cast(@curSize as varchar(10))+'MB'
while (@curSize>@TargetSize) and (@maxTime>0)
begin
backup log @DatabaseName with no_log
DBCC SHRINKFILE(@LogName,@TargetSize)

set @curSize=(select size from sysfiles where name=@LogName)/128
set @maxTime=@maxTime-1
end
set @curSize=(select size from sysfiles where name=@LogName)/128
print '收缩之后的日志文件的大小是：'+cast(@curSize as varchar(10))+'MB'
end
*/
==================第二种==========================================
-- ConfigureDistribution.sql
-- Scripting replication configuration for server CA\SQLA.
-- Installing the server CA\SQLA as a Distributor.
use master
GO
exec sp_adddistributor @distributor = N'CA\SQLA', @password = N''
GO
exec sp_adddistributiondb @database = N'distribution'
, @data_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data'
, @data_file_size = 4
, @log_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data'
, @log_file_size = 2
, @min_distretention = 0
, @max_distretention = 72
, @history_retention = 48
, @security_mode = 1
GO
use [distribution]

if (not exists (select * from sysobjects where name = 'UIProperties' and type = 'U '))
create table UIProperties(id int)
if (exists (select * from ::fn_listextendedproperty
('SnapshotFolder', 'user', 'dbo', 'table', 'UIProperties', null, null)))
EXEC sp_updateextendedproperty N'SnapshotFolder', N'\\Ca\ReplData', 'user',
dbo, 'table', 'UIProperties'
else
EXEC sp_addextendedproperty N'SnapshotFolder', '\\Ca\ReplData', 'user',
dbo, 'table', 'UIProperties'
GO
exec sp_adddistpublisher @publisher = N'CA\SQLA'
, @distribution_db = N'distribution'
, @security_mode = 1
, @working_directory = N'\\Ca\ReplData'
, @trusted = N'false'
, @thirdparty_flag = 0
, @publisher_type = N'MSSQLSERVER'
GO
-- CreatePublication.sql
use [TestDB]
exec sp_replicationdboption @dbname = N'TestDB', @optname = N'publish', @value = N'true'
GO
-- Adding the transactional publication
use [TestDB]
exec sp_addpublication @publication = N'TestDB'
, @description = N'Transactional publication of database ''TestDB'' from Publisher ''CA\SQLA''.'
, @sync_method = N'concurrent'
, @retention = 0
, @allow_push = N'true'
, @allow_pull = N'true'
, @allow_anonymous = N'true'
, @enabled_for_internet = N'false'
, @snapshot_in_defaultfolder = N'true'
, @compress_snapshot = N'false'
, @ftp_port = 21
, @ftp_login = N'anonymous'
, @allow_subscription_copy = N'false'
, @add_to_active_directory = N'false'
, @repl_freq = N'continuous'
, @status = N'active'
, @independent_agent = N'true'
, @immediate_sync = N'true'
, @allow_sync_tran = N'false'
, @autogen_sync_procs = N'false'
, @allow_queued_tran = N'false'
, @allow_dts = N'false'
, @replicate_ddl = 1
, @allow_initialize_from_backup = N'false'
, @enabled_for_p2p = N'false'
, @enabled_for_het_sub = N'false'
GO
exec sp_addpublication_snapshot @publication = N'TestDB'
, @frequency_type = 1
, @frequency_interval = 0
, @frequency_relative_interval = 0
, @frequency_recurrence_factor = 0
, @frequency_subday = 0
, @frequency_subday_interval = 0
, @active_start_time_of_day = 0
, @active_end_time_of_day = 235959
, @active_start_date = 0
, @active_end_date = 0
, @job_login = null
, @job_password = null
, @publisher_security_mode = 0
, @publisher_login = N'sa'
, @publisher_password = N''
use [TestDB]
exec sp_addarticle @publication = N'TestDB'
, @article = N'Family'
, @source_owner = N'dbo'
, @source_object = N'Family'
, @type = N'logbased'
, @description = N''
, @creation_script = null
, @pre_creation_cmd = N'drop'
, @schema_option = 0x000000000803509F
, @identityrangemanagementoption = N'manual'
, @destination_table = N'Family'
, @destination_owner = N'dbo'
, @status = 0
, @vertical_partition = N'false'
, @ins_cmd = N'CALL sp_MSins_dboFamily'
, @del_cmd = N'CALL sp_MSdel_dboFamily'
, @upd_cmd = N'SCALL sp_MSupd_dboFamily'
, @filter_clause = N'[ID] < 100'
-- Adding the article filter
exec sp_articlefilter @publication = N'TestDB'
, @article = N'Family'
, @filter_name = N'FLTR_Family_1__57'
, @filter_clause = N'[ID] < 100'
, @force_invalidate_snapshot = 1
, @force_reinit_subscription = 1

-- Adding the article synchronization object
exec sp_articleview @publication = N'TestDB'
, @article = N'Family'
, @view_name = N'SYNC_Family_1__57'
, @filter_clause = N'[ID] < 100'
, @force_invalidate_snapshot = 1
, @force_reinit_subscription = 1
GO
-- NewSubscription.sql
-- BEGIN: Script to be run at Publisher 'CA\SQLA'
use [TestDB]
exec sp_addsubscription @publication = N'TestDB'
, @subscriber = N'ABCDE\SQL2005'
, @destination_db = N'TestDB'
, @subscription_type = N'Push'
, @sync_type = N'automatic'
, @article = N'all'
, @update_mode = N'read only'
, @subscriber_type = 0

exec sp_addpushsubscription_agent @publication = N'TestDB'
, @subscriber = N'ABCDE\SQL2005'
, @subscriber_db = N'TestDB'
, @job_login = null
, @job_password = null
, @subscriber_security_mode = 0
, @subscriber_login = N'sa'
, @subscriber_password = null
, @frequency_type = 64
, @frequency_interval = 0
, @frequency_relative_interval = 0
, @frequency_recurrence_factor = 0
, @frequency_subday = 0
, @frequency_subday_interval = 0
, @active_start_time_of_day = 0
, @active_end_time_of_day = 235959
, @active_start_date = 20080910
, @active_end_date = 99991231
, @enabled_for_syncmgr = N'False'
, @dts_package_location = N'Distributor'
GO
-- END: Script to be run at Publisher 'CA\SQLA'
--http://www.sqlstudy.com/sql_script_list.php
--sql server 2005
-- 1. 表结构信息查询
-- ========================================================================
-- 表结构信息查询
-- 邹建 2005.08(引用请保留此信息)
-- ========================================================================
SELECT
TableName=CASE WHEN C.column_id=1 THEN O.name ELSE N'' END,
TableDesc=ISNULL(CASE WHEN C.column_id=1 THEN PTB.[value] END,N''),
Column_id=C.column_id,
ColumnName=C.name,
PrimaryKey=ISNULL(IDX.PrimaryKey,N''),
[IDENTITY]=CASE WHEN C.is_identity=1 THEN N'√'ELSE N'' END,
Computed=CASE WHEN C.is_computed=1 THEN N'√'ELSE N'' END,
Type=T.name,
Length=C.max_length,
Precision=C.precision,
Scale=C.scale,
NullAble=CASE WHEN C.is_nullable=1 THEN N'√'ELSE N'' END,
[Default]=ISNULL(D.definition,N''),
ColumnDesc=ISNULL(PFD.[value],N''),
IndexName=ISNULL(IDX.IndexName,N''),
IndexSort=ISNULL(IDX.Sort,N''),
Create_Date=O.Create_Date,
Modify_Date=O.Modify_date
FROM sys.columns C
INNER JOIN sys.objects O
ON C.[object_id]=O.[object_id]
AND O.type='U'
AND O.is_ms_shipped=0
INNER JOIN sys.types T
ON C.user_type_id=T.user_type_id
LEFT JOIN sys.default_constraints D
ON C.[object_id]=D.parent_object_id
AND C.column_id=D.parent_column_id
AND C.default_object_id=D.[object_id]
LEFT JOIN sys.extended_properties PFD
ON PFD.class=1
AND C.[object_id]=PFD.major_id
AND C.column_id=PFD.minor_id
-- AND PFD.name='Caption' -- 字段说明对应的描述名称(一个字段可以添加多个不同name的描述)
LEFT JOIN sys.extended_properties PTB
ON PTB.class=1
AND PTB.minor_id=0
AND C.[object_id]=PTB.major_id
-- AND PFD.name='Caption' -- 表说明对应的描述名称(一个表可以添加多个不同name的描述)
LEFT JOIN -- 索引及主键信息
(
SELECT
IDXC.[object_id],
IDXC.column_id,
Sort=CASE INDEXKEY_PROPERTY(IDXC.[object_id],IDXC.index_id,IDXC.index_column_id,'IsDescending')
WHEN 1 THEN 'DESC' WHEN 0 THEN 'ASC' ELSE '' END,
PrimaryKey=CASE WHEN IDX.is_primary_key=1 THEN N'√'ELSE N'' END,
IndexName=IDX.Name
FROM sys.indexes IDX
INNER JOIN sys.index_columns IDXC
ON IDX.[object_id]=IDXC.[object_id]
AND IDX.index_id=IDXC.index_id
LEFT JOIN sys.key_constraints KC
ON IDX.[object_id]=KC.[parent_object_id]
AND IDX.index_id=KC.unique_index_id
INNER JOIN -- 对于一个列包含多个索引的情况,只显示第1个索引信息
(
SELECT [object_id], Column_id, index_id=MIN(index_id)
FROM sys.index_columns
GROUP BY [object_id], Column_id
) IDXCUQ
ON IDXC.[object_id]=IDXCUQ.[object_id]
AND IDXC.Column_id=IDXCUQ.Column_id
AND IDXC.index_id=IDXCUQ.index_id
) IDX
ON C.[object_id]=IDX.[object_id]
AND C.column_id=IDX.column_id
-- WHERE O.name=N'要查询的表' -- 如果只查询指定表,加上此条件
ORDER BY O.name,C.column_id

-- 2. 索引及主键信息
-- ========================================================================
-- 索引及主键信息
-- 邹建 2005.08(引用请保留此信息)
-- ========================================================================
SELECT
TableId=O.[object_id],
TableName=O.Name,
IndexId=ISNULL(KC.[object_id],IDX.index_id),
IndexName=IDX.Name,
IndexType=ISNULL(KC.type_desc,'Index'),
Index_Column_id=IDXC.index_column_id,
ColumnID=C.Column_id,
ColumnName=C.Name,
Sort=CASE INDEXKEY_PROPERTY(IDXC.[object_id],IDXC.index_id,IDXC.index_column_id,'IsDescending')
WHEN 1 THEN 'DESC' WHEN 0 THEN 'ASC' ELSE '' END,
PrimaryKey=CASE WHEN IDX.is_primary_key=1 THEN N'√'ELSE N'' END,
[UQIQUE]=CASE WHEN IDX.is_unique=1 THEN N'√'ELSE N'' END,
Ignore_dup_key=CASE WHEN IDX.ignore_dup_key=1 THEN N'√'ELSE N'' END,
Disabled=CASE WHEN IDX.is_disabled=1 THEN N'√'ELSE N'' END,
Fill_factor=IDX.fill_factor,
Padded=CASE WHEN IDX.is_padded=1 THEN N'√'ELSE N'' END
FROM sys.indexes IDX
INNER JOIN sys.index_columns IDXC
ON IDX.[object_id]=IDXC.[object_id]
AND IDX.index_id=IDXC.index_id
LEFT JOIN sys.key_constraints KC
ON IDX.[object_id]=KC.[parent_object_id]
AND IDX.index_id=KC.unique_index_id
INNER JOIN sys.objects O
ON O.[object_id]=IDX.[object_id]
INNER JOIN sys.columns C
ON O.[object_id]=C.[object_id]
AND O.type='U'
AND O.is_ms_shipped=0
AND IDXC.Column_id=C.Column_id
-- INNER JOIN -- 对于一个列包含多个索引的情况,只显示第1个索引信息
-- (
-- SELECT [object_id], Column_id, index_id=MIN(index_id)
-- FROM sys.index_columns
-- GROUP BY [object_id], Column_id
-- ) IDXCUQ
-- ON IDXC.[object_id]=IDXCUQ.[object_id]
-- AND IDXC.Column_id=IDXCUQ.Column_id
/**************************
系统数据库中查询表的所有字段以及描述（2008，2000区分）以及查询表的外键
***************************/
----2008下
-------方法一----表的扩展属性01------
SELECT *
FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', 't_bill_in', 'column', default)

SELECT
CAST(value AS nvarchar(200)) as tableDescription
FROM fn_listextendedproperty ('MS_Description', 'user', 'dbo', 'table', 'T_Bill_Cedula_Detail', default, default);

-----方法二----表的扩展属性描述-----
SELECT
表名 = case when a.colorder=1 then d.name else '' end,
表说明 = case when a.colorder=1 then isnull(f.value,'') else '' end,
字段序号 = a.colorder,
字段名 = a.name,
标识 = case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end,
主键 = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (
SELECT name FROM sysindexes WHERE indid in(
SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then '√' else '' end,
类型 = b.name,
占用字节数 = a.length,
长度 = COLUMNPROPERTY(a.id,a.name,'PRECISION'),
小数位数 = isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
允许空 = case when a.isnullable=1 then '√'else '' end,
默认值 = isnull(e.text,''),
字段说明 = isnull(g.[value],'')
FROM
syscolumns a
left join
systypes b
on
a.xusertype=b.xusertype
inner join
sysobjects d
on
a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join
syscomments e
on
a.cdefault=e.id
left join
dbo.dtproperties g
on
a.id=g.id and a.colid=g.objectid
left join
dbo.dtproperties f
on
d.id=f.id and f.objectid=0
where
d.name='要查询的表' --如果只查询指定表,加上此条件
order by
a.id,a.colorder

----方法03----表字段的描述（简易）
Select
col.[name] as '字段名',
col.[length]as '长度' ,
type.[name] as '类型' ,
pro.value as '描述'
From syscolumns as col
Left Join systypes as type on col.xtype = type.xtype
Left Join dbo.dtproperties as pro on col.id = pro.id and col.colid = pro.objectid
where col.id = (Select id From Sysobjects Where name = 'T_DeptClass')

---2000下------------------------
SELECT
表名 = case when a.colorder=1 then d.name else '' end,
表说明 = case when a.colorder=1 then isnull(f.value,'') else '' end,
字段序号 = a.colorder,
字段名 = a.name,
标识 = case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end,
主键 = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (
SELECT name FROM sysindexes WHERE indid in(
SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then '√' else '' end,
类型 = b.name,
占用字节数 = a.length,
长度 = COLUMNPROPERTY(a.id,a.name,'PRECISION'),
小数位数 = isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
允许空 = case when a.isnullable=1 then '√'else '' end,
默认值 = isnull(e.text,''),
字段说明 = isnull(g.[value],'')
FROM
syscolumns a
left join
systypes b
on
a.xusertype=b.xusertype
inner join
sysobjects d
on
a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join
syscomments e
on
a.cdefault=e.id
left join
sysproperties g
on
a.id=g.id and a.colid=g.smallid
left join
sysproperties f
on
d.id=f.id and f.smallid=0
where
d.name='要查询的表' --如果只查询指定表,加上此条件
order by
a.id,a.colorder
=-----------方法02---表的描述2000下----
Select
col.[name] as '字段名',
col.[length]as '长度' ,
type.[name] as '类型' ,
pro.value as '描述'
From syscolumns as col
Left Join systypes as type on col.xtype = type.xtype
Left Join sysProperties as pro on col.id = pro.id and col.colid = pro.smallid
where col.id = (Select id From Sysobjects Where name = 'T_DeptClass')

-----------------查询一个表的所有外键
SELECT 主键列ID=b.rkey
,主键列名=(SELECT name FROM syscolumns WHERE colid=b.rkey AND id=b.rkeyid)
,外键表ID=b.fkeyid
,外键表名称=object_name(b.fkeyid)
,外键列ID=b.fkey
,外键列名=(SELECT name FROM syscolumns WHERE colid=b.fkey AND id=b.fkeyid)
,级联更新=ObjectProperty(a.id,'CnstIsUpdateCascade')
,级联删除=ObjectProperty(a.id,'CnstIsDeleteCascade')
FROM sysobjects a
join sysforeignkeys b on a.id=b.constid
join sysobjects c on a.parent_obj=c.id
where a.xtype='f' AND c.xtype='U'
and object_name(b.rkeyid)='titles'

SELECT *
FROM information_schema.columns
WHERE TABLE_CATALOG='数据库名'
AND TABLE_NAME = '表名'
AND COLUMN_NAME='列名'

select *
from syscolumns
where id=object_id('tableName') and name='fieldName'

------------2005以及2008中，查询表的字段---------------------
DECLARE @tableName nvarchar(100)
SET @tableName ='tab'
SELECT (
CASE WHEN a.colorder=1 THEN d.name ELSE '' END)表名,
a.colorder 字段序号, a.name 字段名,
(CASE WHEN COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 THEN '√' ELSE '' END) 标识,
(CASE WHEN ( SELECT COUNT(*) FROM sysobjects WHERE (name IN (SELECT name FROM sysindexes WHERE (id = a.id) AND (indid IN (SELECT indid FROM sysindexkeys WHERE (id = a.id) AND (colid IN (SELECT colid FROM syscolumns WHERE (id = a.id) AND (name = a.name))))))) AND (xtype = 'PK'))>0 THEN '√' ELSE '' END) 主键,
b.name 类型,
a.length 占用字节数,
COLUMNPROPERTY(a.id,a.name,'PRECISION') AS 长度,
ISNULL(COLUMNPROPERTY(a.id,a.name,'Scale'),0) AS 小数位数,
(CASE WHEN a.isnullable=1 THEN '√' ELSE '' END) 允许空,
ISNULL(e.text,'') 默认值, ISNULL(g.[value],'') AS 字段说明
FROM syscolumns a
LEFT JOIN systypes b ON a.xtype=b.xusertype
INNER JOIN sysobjects d ON a.id=d.id AND d.xtype='U' AND d.name <>'dtproperties'
LEFT JOIN syscomments e ON a.cdefault=e.id
LEFT JOIN sys.extended_properties g ON a.id=g.major_id AND a.colid = g.major_id WHERE d.name=@tableName
ORDER BY a.id,a.colorder
SELECT
CAST(value AS nvarchar(200)) as tableDescription
FROM fn_listextendedproperty ('MS_Description', 'user', 'dbo', 'table', 't_bank', default, default);
declare @tablename varchar(100)
set @tablename=''
SELECT
objname
,CAST(value AS nvarchar(200)) as fieldDescription
FROM fn_listextendedproperty ('MS_Description', 'user', 'dbo', 'table', @TableName , 'column', default) AS E
=====================================================================================================================
SQL2005自动备份和自动删除三天前的备份
1 declare @data_3ago nvarchar(50)
2 declare @cmd varchar(50)
3
4 set @data_3ago ='e:\data\'+convert(varchar(10),getdate()-3,112)
5 set @cmd = 'del '+ @data_3ago
6 exec master..xp_cmdshell @cmd
7 go
8
9 declare @data nvarchar(50)
10 set @data='e:\data\'+convert(varchar(10),getdate(),112)
11 BACKUP DATABASE job TO DISK = @data
12 with init
===================================================
.获取表的基本字段属性
--获取SqlServer中表结构
SELECT syscolumns.name,systypes.name,syscolumns.isnullable,
syscolumns.length
FROM syscolumns, systypes
WHERE syscolumns.xusertype = systypes.xusertype
AND syscolumns.id = object_id('你的表名')
运行效果
2.如果还想要获取字段的描述信息则
--获取SqlServer中表结构主键，及描述
declare @table_name as varchar(max)
set @table_name = '你的表名'
select sys.columns.name, sys.types.name, sys.columns.max_length, sys.columns.is_nullable,
(select count(*) from sys.identity_columns where sys.identity_columns.object_id = sys.columns.object_id and sys.columns.column_id = sys.identity_columns.column_id) as is_identity ,
(select value from sys.extended_properties where sys.extended_properties.major_id = sys.columns.object_id and sys.extended_properties.minor_id = sys.columns.column_id) as description
from sys.columns, sys.tables, sys.types where sys.columns.object_id = sys.tables.object_id and sys.columns.system_type_id=sys.types.system_type_id and sys.tables.name=@table_name order by sys.columns.column_id

运行效果

3.单独查询表的递增字段

--单独查询表递增字段
select [name] from syscolumns where
id=object_id(N'你的表名') and COLUMNPROPERTY(id,name,'IsIdentity')=1
运行效果

4.获取表的主外键
--获取表主外键约束
exec sp_helpconstraint '你的表名' ;
运行效果
==============================================

/*查看数据库脱机时间*/
/*author lcw 2008-10-21*/
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
go
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
GO
select a.name,a.database_id,a.create_date,b.physical_name into #a
from sys.databases a left join sys.master_files b on
a.database_id=b.database_id where has_dbaccess(a.name)<>1 and b.type=1

create table #b(info varchar(500))
declare @string varchar(max)
set @string=''
select @string=@string+'insert into #b exec xp_cmdshell''dir '+ physical_name +''''+char(13)+char(10) from #a
execute(@string)

select a.name,substring(b.info,0,20) as 脱机时间,a.database_id,a.create_date,a.physical_name
from #a a left join #b b on
REVERSE(substring(REVERSE(physical_name),0,charindex('\',REVERSE(physical_name))))
=REVERSE(substring(REVERSE(info),0,charindex(' ',REVERSE(info))))

drop table #a,#b
go
EXEC sp_configure 'xp_cmdshell', 0
RECONFIGURE
go
EXEC sp_configure 'show advanced options', 0
RECONFIGURE
go

=======================================

--查看作业执行情况
select category = jc.name,
category_id = jc.category_id,
job_name = j.name,
job_enabled = j.enabled,
last_run_time = cast(js.last_run_date as varchar(10)) + '-' + cast(js.last_run_time as varchar(10)),
last_run_duration = js.last_run_duration,
last_run_status = js.last_run_outcome,
last_run_msg = js.last_outcome_message + cast(nullif(js.last_run_outcome,1) as varchar(2)),
job_created = j.date_created,
job_modified = j.date_modified
from msdb.dbo.sysjobs j
inner join msdb.dbo.sysjobservers js
on j.job_id = js.job_id
inner join msdb.dbo.syscategories jc
on j.category_id = jc.category_id
where j.enabled = 1
and js.last_run_outcome in (0,1,3,5) -- 0:Fail 1:Succ 3:Cancel 5:First run
and jc.category_id not between 10 and 20 -- repl
=================================================

/*========================================================
过程描述：实现SQL2005 数据库文件移动到指定目录路径
创建者： LCW
创建日期：2008-07-29
===========================================================*/
USE master
GO
DECLARE
@DBName sysname,
@DestPath varchar(256),
@DestPath1 varchar(256)
DECLARE @DB table(
name sysname,
physical_name sysname)
BEGIN TRY
SELECT
@DBName = '', --input database name
@DestPath = 'K:\data\', --input destination DATA path
@DestPath1 = 'L:\LOG\' --input destination LOG path
--kill database processes
DECLARE @SPID varchar(20)
DECLARE curProcess CURSOR FOR
SELECT spid
FROM sys.sysprocesses
WHERE DB_NAME(dbid) = @DBName
OPEN curProcess
FETCH NEXT FROM curProcess INTO @SPID
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC('KILL ' + @SPID)
FETCH NEXT FROM curProcess
END
CLOSE curProcess
DEALLOCATE curProcess
--query physical name
INSERT @DB(
name,
physical_name)
SELECT
A.name,
A.physical_name
FROM sys.master_files A
INNER JOIN sys.databases B
ON A.database_id = B.database_id
AND B.name = @DBName
WHERE A.type <=1
--set offline
EXEC('ALTER DATABASE [' + @DBName + '] SET OFFLINE')
--move to dest path
DECLARE
@login_name sysname,
@physical_name sysname,
@temp_name varchar(256)
DECLARE curMove CURSOR FOR
SELECT
name,
physical_name
FROM @DB
OPEN curMove
FETCH NEXT FROM curMove INTO @login_name,@physical_name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @temp_name = RIGHT(@physical_name,CHARINDEX('\',REVERSE(@physical_name)) - 1)
IF RIGHT(RTRIM(@TEMP_NAME),3)='LDF'
BEGIN
EXEC('exec xp_cmdshell ''move "' + @physical_name + '" "' + @DestPath1 + '"''')
EXEC('ALTER DATABASE [' + @DBName + '] MODIFY FILE ( NAME = [' + @login_name
+ '], FILENAME = ''' + @DestPath1 + @temp_name + ''')')
END
ELSE
BEGIN
EXEC('exec xp_cmdshell ''move "' + @physical_name + '" "' + @DestPath + '"''')
EXEC('ALTER DATABASE [' + @DBName + '] MODIFY FILE ( NAME = [' + @login_name
+ '], FILENAME = ''' + @DestPath + @temp_name + ''')')
END
FETCH NEXT FROM curMove INTO @login_name,@physical_name
END
CLOSE curMove
DEALLOCATE curMove
--set online
EXEC('ALTER DATABASE [' + @DBName + '] SET ONLINE')
--show result
SELECT
A.name,
A.physical_name
FROM sys.master_files A
INNER JOIN sys.databases B
ON A.database_id = B.database_id
AND B.name = @DBName
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS ErrorMessage
END CATCH

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23490154/viewspace-1398899/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23490154/viewspace-1398899/