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<[email protected]> * Version : 00 * Date : 2007-09-18 * Re : * *******************************************************************************/ use masterdeclare @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<[email protected]> * Version : 00 * Date : 2007-09-16 * Re : * *************************************************************************/-- --第一步：设置需要收缩的数据库，找到需要收缩数据文件 --use cctqa select Size/128 Size,Name from sysfiles/**//*declare @LogName varchar(255),@TargetSize int-- -- --第二步：设置需要收缩的日志文件的逻辑名字和收缩后的大小 --千万不要搞错了，选错了文件，有可能会丢失数据，那时候 --哭都哭不回来了。 -- --set @LogName='CCTQA_Log' set @TargetSize=1declare @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 */
select xtype,count(*) from sysobjects group by xtype--參照聯機sysobjects
--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
DECLARE @tableName nvarchar(100) SET @tableName ='jzs_CodeList' 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,'') 默认值, 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.minor_id WHERE d.name=@tableName ORDER BY a.id,a.colorder
if object_id('tb')is not null drop table tb go create table tb( 表名 sysname, 记录数 int, 保留空间 nvarchar(10), 使用空间 varchar(10), 索引使用空间 varchar(10), 未用空间 varchar(10)) exec sp_MSForEachTable @command1=N'insert tb exec sp_spaceused ''?''' select * from tb http://www.yesky.com/imagesnew/software/tsql/ts_sp_sa-sz_0kro.htm --关于 sp_spaceused 查看表占用空间
/* *利用sys.indexes sys.partitions sys.allocation_units 三个视图 *显示:表名 索引名 类型 页面类型 空间使用情况等信息 */ USE tempdb GO CREATE TABLE dbo.employee ( name varchar(15) NOT NULL, address varchar(30) NOT NULL, phone char(12) NOT NULL, job_level smallint NOT NULL ); GO ALTER TABLE dbo.employee ADD resume_short varchar(8000); ALTER TABLE dbo.employee ADD resume_long text; GO alter table dbo.employee add constraint PR_NAME primary key(name); CREATE NONCLUSTERED INDEX UN_IN_k ON dbo.employee(PHONE) GO insert dbo.employee select 'poofly','sadsadsa','111111111111',1,'asdsadsadsadsa','dsadsa' union select 'lo','sad','222222222222',1,'asa','s' GO SELECT convert(char(8),object_name(i.object_id)) AS table_name, i.name AS index_name, i.index_id, i.type_desc as index_type, partition_id, partition_number AS pnum, rows, allocation_unit_id AS au_id, a.type_desc as page_type_desc, total_pages AS pages FROM sys.indexes i JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id JOIN sys.allocation_units a ON p.partition_id = a.container_id WHERE i.object_id=object_id('dbo.employee'); GO /* table_name index_name index_id index_type partition_id pnum rows au_id page_type_desc pages ---------- -------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------------------------------------------------ -------------------- ----------- -------------------- -------------------- ------------------------------------------------------------ -------------------- employee PR_NAME 1 CLUSTERED 792633534827724800 1 2 936748722986549248 IN_ROW_DATA 2 employee PR_NAME 1 CLUSTERED 792633534827724800 1 2 864691128907661312 LOB_DATA 2 employee PR_NAME 1 CLUSTERED 792633534827724800 1 2 1008806317065437184 ROW_OVERFLOW_DATA 0 employee UN_IN_k 2 NONCLUSTERED 864691128906612736 1 2 1080863911144325120 IN_ROW_DATA 2 */ 查询索引信息
SELECT convert(char(7), object_name(object_id)) AS name, partition_id, partition_number AS pnum, rows, allocation_unit_id AS au_id, convert(char(17), type_desc) as page_type_desc, total_pages AS pages FROM sys.partitions p JOIN sys.allocation_units a ON p.partition_id = a.container_id WHERE object_id=object_id('dbo.bigrows'); 表的页面存储状况
本科学历，地理信息相关专业最好 英文流利 3年数据库相关工作经验，并懂得Arcgis。有在制图方面相关工作经验的优先考虑。 • Experience in Oracle database, basic SQL skill and statistics • Ability to form and maintain relationship with the Chinese partner and the joint venture • Flexible to work in multiple time-zones and ability to travel without restriction within China and internationally 欢迎联系 [email protected]
-- ======================================================================== -- 表结构信息查询 -- 邹建 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
对我有
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 '你的表名' ; 运行效果
论坛里有sql2000基础学习介绍吗
/*查看数据库脱机时间*/ /*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=1create 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 GODECLARE @DBName sysname, @DestPath varchar(256), @DestPath1 varchar(256) DECLARE @DB table( name sysname, physical_name sysname) BEGIN TRYSELECT @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) = @DBNameOPEN 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 nameINSERT @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 TRYBEGIN CATCH SELECT ERROR_MESSAGE() AS ErrorMessage END CATCH
---trysysaltfiles 主数据库 保存数据库的文件
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<[email protected]>
* Date : 2005-5-10 2005-5-19 2006-8-1 2007-09-18
* Version : 00 01 02 03
*
* Re :
* 2006-08-01 增加差异备份和完全备份两种情况,生成多个备份副本
* 2008-09-18 增加备份一个实例中的所有数据库的情况,并在备份后清除日志
*
*********************************************************************************/use masterdeclare @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<=4open cur_database fetch next from cur_database
into @DbNamewhile @@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<[email protected]>
* Version : 00
* Date : 2007-09-18
* Re :
*
*******************************************************************************/
use masterdeclare
@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<[email protected]>
* Version : 00
* Date : 2007-09-16
* Re :
*
*************************************************************************/--
--第一步：设置需要收缩的数据库，找到需要收缩数据文件
--use cctqa
select Size/128 Size,Name from sysfiles/**//*declare @LogName varchar(255),@TargetSize int--
--
--第二步：设置需要收缩的日志文件的逻辑名字和收缩后的大小
--千万不要搞错了，选错了文件，有可能会丢失数据，那时候
--哭都哭不回来了。
--
--set @LogName='CCTQA_Log'
set @TargetSize=1declare @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
GOexec sp_adddistributor @distributor = N'CA\SQLA', @password = N''
GOexec 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
GOuse [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'
GOexec 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.sqluse [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 = 0exec 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
-- 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
SET @tableName ='jzs_CodeList'
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,'') 默认值, 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.minor_id
WHERE d.name=@tableName
ORDER BY a.id,a.colorder
4.查看数据库启动时间 . select convert(varchar(30),login_time,120) from master..sysprocesses where spid=1 查看数据库服务器名和实例名 . print ''Server Name...............: '' + convert(varchar(30),@@SERVERNAME) . print ''Instance..................: '' + convert(varchar(30),@@SERVICENAME) ... 5. 查看所有数据库名称及大小sp_helpdb 。 重命名数据库用的SQL sp_renamedb ''old_dbname'', ''new_dbname''6. 查看所有数据库用户登录信息 .. sp_helplogins查看所有数据库用户所属的角色信息sp_helpsrvrolemember ! 修复迁移服务器时孤立用户时,可以用的fix_orphan_user脚本或者LoneUser过程 . 更改某个数据对象的用户属主 sp_changeobjectowner [@objectname =] ''object'', [@newowner =] ''owner'' . 注意: 更改对象名的任一部分都可能破坏脚本和存储过程。把一台服务器上的数据库用户登录信息备份出来可以用add_login_to_aserver脚本7. 查看链接服务器 ... sp_helplinkedsrvlogin查看远端数据库用户登录信息 。 sp_helpremotelogin .. 8.查看某数据库下某个数据对象的大小 ! sp_spaceused @objname 还可以用sp_toptables过程看最大的N(默认为50)
go
create table tb(
表名 sysname,
记录数 int,
保留空间 nvarchar(10),
使用空间 varchar(10),
索引使用空间 varchar(10),
未用空间 varchar(10))
exec sp_MSForEachTable @command1=N'insert tb exec sp_spaceused ''?'''
select * from tb
http://www.yesky.com/imagesnew/software/tsql/ts_sp_sa-sz_0kro.htm --关于 sp_spaceused
查看表占用空间
*利用sys.indexes sys.partitions sys.allocation_units 三个视图
*显示:表名 索引名 类型 页面类型 空间使用情况等信息
*/
USE tempdb
GO
CREATE TABLE dbo.employee (
name varchar(15) NOT NULL,
address varchar(30) NOT NULL,
phone char(12) NOT NULL,
job_level smallint NOT NULL
);
GO
ALTER TABLE dbo.employee ADD resume_short varchar(8000);
ALTER TABLE dbo.employee ADD resume_long text;
GO
alter table dbo.employee add constraint PR_NAME primary key(name);
CREATE NONCLUSTERED INDEX UN_IN_k ON dbo.employee(PHONE)
GO
insert dbo.employee
select 'poofly','sadsadsa','111111111111',1,'asdsadsadsadsa','dsadsa'
union
select 'lo','sad','222222222222',1,'asa','s'
GO
SELECT
convert(char(8),object_name(i.object_id)) AS table_name,
i.name AS index_name,
i.index_id,
i.type_desc as index_type,
partition_id,
partition_number AS pnum,
rows,
allocation_unit_id AS au_id,
a.type_desc as page_type_desc,
total_pages AS pages
FROM sys.indexes i JOIN sys.partitions p
ON i.object_id = p.object_id AND i.index_id = p.index_id
JOIN sys.allocation_units a
ON p.partition_id = a.container_id
WHERE i.object_id=object_id('dbo.employee');
GO
/*
table_name index_name index_id index_type partition_id pnum rows au_id page_type_desc pages
---------- -------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------------------------------------------------ -------------------- ----------- -------------------- -------------------- ------------------------------------------------------------ --------------------
employee PR_NAME 1 CLUSTERED 792633534827724800 1 2 936748722986549248 IN_ROW_DATA 2
employee PR_NAME 1 CLUSTERED 792633534827724800 1 2 864691128907661312 LOB_DATA 2
employee PR_NAME 1 CLUSTERED 792633534827724800 1 2 1008806317065437184 ROW_OVERFLOW_DATA 0
employee UN_IN_k 2 NONCLUSTERED 864691128906612736 1 2 1080863911144325120 IN_ROW_DATA 2
*/
查询索引信息
partition_id, partition_number AS pnum, rows,
allocation_unit_id AS au_id, convert(char(17), type_desc) as page_type_desc,
total_pages AS pages
FROM sys.partitions p JOIN sys.allocation_units a
ON p.partition_id = a.container_id
WHERE object_id=object_id('dbo.bigrows');
表的页面存储状况
报告有关数据库对象（sysobjects 表中列出的任何对象）、用户定义数据类型或 Microsoft® SQL Server™ 所提供的数据类型的信息。
因公司正在急招员工，新员工试用任务是帮助公司发30条招聘消息，完成后才能转为正式员工，系统会自动跟踪您的宣传网址。完成后自动转入后台，即可成为TvNets的正式员工。新员工此次试用任务工资100元。请尽快完成，工资次日结算！ 温馨提示：发招聘消息可到各大兼职网站、兼职论坛、各地人才网、各地招聘网、高校论坛、其他著名论坛，或QQ群邮件。宣传内容：可以参考以下内容或者自己编写，注意：网址改成您的宣传网址，否则系统无法跟踪统计！--------------------------------------《宣传标题》-------------------------------------- 标题：3000元/月急聘兼职网络信息回复员，工资日结标题：tvnets公司急聘兼职网络信息回复员，100元/天！标题：100元/天急聘兼职网络信息回复员（若干名）工资日结--------------------------------------《宣传内容》---------------------------------------
英文流利
3年数据库相关工作经验，并懂得Arcgis。有在制图方面相关工作经验的优先考虑。
• Experience in Oracle database, basic SQL skill and statistics
• Ability to form and maintain relationship with the Chinese partner and the joint venture
• Flexible to work in multiple time-zones and ability to travel without restriction within China and internationally
欢迎联系 [email protected]
-- 表结构信息查询
-- 邹建 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 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=1create 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
GODECLARE
@DBName sysname,
@DestPath varchar(256),
@DestPath1 varchar(256)
DECLARE @DB table(
name sysname,
physical_name sysname)
BEGIN TRYSELECT
@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) = @DBNameOPEN 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 nameINSERT @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 TRYBEGIN CATCH
SELECT ERROR_MESSAGE() AS ErrorMessage
END CATCH