求一些常用的SQL(05版以上)数据库维护脚本
比如查看表数量 存储过程数量
锁状态
重建索引
镜像等等 要脚本代码 谢谢
帖子会再另开帖,再加400分,代码越实用越好..
代码回复在本帖就好.
谢谢
[解决办法]
- SQL code
---trysysaltfiles 主数据库 保存数据库的文件syscharsets 主数据库 字符集与排序顺序sysconfigures 主数据库 配置选项syscurconfigs 主数据库 当前配置选项sysdatabases 主数据库 服务器中的数据库syslanguages 主数据库 语言syslogins 主数据库 登陆帐号信息sysoledbusers 主数据库 链接服务器登陆信息sysprocesses 主数据库 进程sysremotelogins 主数据库 远程登录帐号syscolumns 每个数据库 列sysconstrains 每个数据库 限制sysfilegroups 每个数据库 文件组sysfiles 每个数据库 文件sysforeignkeys 每个数据库 外部关键字sysindexs 每个数据库 索引sysmembers 每个数据库 角色成员sysobjects 每个数据库 所有数据库对象syspermissions 每个数据库 权限systypes 每个数据库 用户定义数据类型sysusers 每个数据库 用户
[解决办法]
- SQL code
/**//********************************************************************************* * FielName : backup.sql * Function : 自动备份 * Author : Yahong<Yahongq111@163.com>* 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 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_nameset @dir_db=@dir+'Database'set @verb='mkdir '--建立目录set @cmd=@verb+@dir_dbexec xp_cmdshell @cmddeclare cur_database cursor forward_only read_only forselect name from sysdatabaseswhere dbid>4 --系统数据库的dbid<=4open cur_database fetch next from cur_databaseinto @DbNamewhile @@fetch_status=0begin 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 @DbNameendclose cur_databasedeallocate cur_database--删除网络连接exec xp_cmdshell 'net use K: /delete'exec xp_cmdshell 'net use I: /delete'/**//******************************************************************************** File Name : Restore.sql* Function : 数据库还原* Author : Yahong<Yahongq111@163.com> * Version : 00* Date : 2007-09-18* Remark :********************************************************************************/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+'\'+@MasterFileNameset @TargetLogFileName=@TargetDir+'\'+@LogFileName--添加还原设备set @WholeDeviceName=@DbName+'WholeDevice'set @WholeFileName='K:\'+@WholeFileNameexec 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 @DifferenctDeviceNameendelsebegin --只有完全备份的还原 if(isnull(@TargetDir,'')='') restore database @DbName from @WholeFileName else restore database @DbName from @WholeFileName with move @MasterFileName to @TargetMasterFileName, move @LogFileName to @TargetLogFileNameend--释放备份设备exec sp_dropdevice @WholeDeviceNameif(isnull(@DifferentFileName,'')<>'') exec sp_dropdevice @DifferenctDeviceName--删除网络链接exec xp_cmdshell 'net use K: /delete'/**//************************************************************************** File Name : ShrinkLog.sql* Function : 收缩数据库的日志文件* Author ：Yahong<Yahongq111@163.com>* Version : 00* Date : 2007-09-16* Remark :**************************************************************************/----第一步：设置需要收缩的数据库，找到需要收缩数据文件--use cctqaselect 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 elsebegin 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 code
-- ConfigureDistribution.sql-- Scripting replication configuration for server CA\SQLA. -- Installing the server CA\SQLA as a Distributor. use masterGOexec 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 = 1GOuse [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 publicationuse [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'GOexec 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 filterexec 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 objectexec 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 = 1GO-- 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'
[解决办法]
- SQL code
--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_dateFROM 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'' ENDFROM 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
[解决办法]
- SQL code
/**************************系统数据库中查询表的所有字段以及描述（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 aleft join systypes b on a.xusertype=b.xusertypeinner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'left join syscomments e on a.cdefault=e.idleft 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=0where 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 aleft join systypes b on a.xusertype=b.xusertypeinner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'left join syscomments e on a.cdefault=e.idleft 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=0where 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.columnsWHERE TABLE_CATALOG='数据库名' AND TABLE_NAME = '表名' AND COLUMN_NAME='列名'select *from syscolumnswhere 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
[解决办法]
顶完再围观
[解决办法]
同学习，马克.....
[解决办法]
- SQL code
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
[解决办法]
- SQL code
if object_id('tb')is not null drop table tbgocreate table tb(表名 sysname,记录数 int,保留空间 nvarchar(10),使用空间 varchar(10),索引使用空间 varchar(10),未用空间 varchar(10))exec sp_MSForEachTable @command1=N'insert tb exec sp_spaceused ''?'''select * from tbhttp://www.yesky.com/imagesnew/software/tsql/ts_sp_sa-sz_0kro.htm --关于 sp_spaceused
[解决办法]
g跟着学习一下
[解决办法]
学学！
[解决办法]
这么多了?帮顶.
[解决办法]
都快忘没了。mark .thanks
[解决办法]
学到东西了，谢谢！！！！
[解决办法]
很好 不错呢 呵呵
[解决办法]
HAOHAOHAOHAOHAOH
[解决办法]
不错啦
[解决办法]
膜拜各位
[解决办法]
不错啦
[解决办法]
- SQL code
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 pagesFROM sys.partitions p JOIN sys.allocation_units a ON p.partition_id = a.container_idWHERE object_id=object_id('dbo.bigrows');
[解决办法]
一天学一点！！!
------解决方案--------------------
[解决办法]
好啊！真的很好，这贴要顶！
[解决办法]
收藏，学习！
[解决办法]
学习，收藏，顶
学习，收藏，顶
[解决办法]
收藏，学习！
[解决办法]
dddddddddddddddd
[解决办法]
bless!
[解决办法]
XXXXXX
[解决办法]
关注一下。学习学习
[解决办法]
xxxx
[解决办法]
关注一下。学习学习
[解决办法]
O(∩_∩)O谢谢
[解决办法]
收藏学习，mark！！
[解决办法]
顶。学习。
[解决办法]
学习 学习
[解决办法]
不错学习学习
[解决办法]
学习了 谢谢
[解决办法]
好多~~收藏了~~
[解决办法]
sp_help
报告有关数据库对象（sysobjects 表中列出的任何对象）、用户定义数据类型或 Microsoft® SQL Server? 所提供的数据类型的信息。
[解决办法]
保存，看看
[解决办法]
顶.....
[解决办法]
接触到从未涉及的SQL
[解决办法]
学习 学习
[解决办法]
很好，不错的东西受教了
[解决办法]
太棒了
一下了解了这么多东西
[解决办法]
[解决办法]
先收藏起来。
[解决办法]
数据库 博大精深啊
[解决办法]
已收藏了。非常感谢楼上的各位提供。
[解决办法]
路过学习
[解决办法]
不好西
------解决方案--------------------
learning......
[解决办法]
学习...
[解决办法]
好东西
[解决办法]
学习，马克.....
[解决办法]
每天回帖即可获得10分可用分
[解决办法]
本科学历，地理信息相关专业最好
英文流利
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
欢迎联系 zcui@goldentophr.com
[解决办法]
看着就心寒....
[解决办法]
- SQL code
-- ========================================================================-- 表结构信息查询-- 邹建 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_dateFROM 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 对我有
[解决办法]
- SQL code
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
[解决办法]
代码解释的详细些，用起来方便，不然半天揣摩需要
[解决办法]
收藏起来！
[解决办法]
好东西 顶。。。
[解决办法]
好东西，随时关注～
[解决办法]
这么多高人都在啊，
啥也不说了，顶..........
[解决办法]
[解决办法]
OMG！
[解决办法]
Mark
[解决办法]
学习 学习 啊
[解决办法]
我也过来学习学习
[解决办法]
好东西，学习了
[解决办法]
论坛里有sql2000基础学习介绍吗
[解决办法]
mark`~
[解决办法]
[解决办法]
学习！！！！！！！！！！！！！
[解决办法]
...............
[解决办法]
.........................
[解决办法]
ggggggggggggggggggggggg
[解决办法]
好东西、、、、不错不错。。。。
[解决办法]
想分页的 没希望
[解决办法]
不错的东西，收藏了。
[解决办法]
要学习的还有好多！！！
[解决办法]
帮顶了
因为我也想要
[解决办法]
这个。。。。。许久不见的大牛们都出现了，一定要顶
[解决办法]
- SQL code
/*查看数据库脱机时间*//*author lcw 2008-10-21*/EXEC sp_configure 'show advanced options', 1 RECONFIGURE goEXEC 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,#bgoEXEC sp_configure 'xp_cmdshell', 0 RECONFIGURE goEXEC sp_configure 'show advanced options', 0 RECONFIGURE go