常用SQL
-- 查看表记录数SELECT a.id, b.[name], a.rowcnt FROM sysindexes a, sys.tables b WHERE a.id = b.[object_id] AND a.indid <=1 ORDER BY b.[name]-- 查看文件组SELECT name, data_space_id 文件组id, size/128 [文件大小(兆)],FILEPROPERTY(name, 'SpaceUsed')/128 [已用空间(兆)],size/128 - FILEPROPERTY(name, 'SpaceUsed')/128 [未用空间(兆)],FILEPROPERTY(name, 'SpaceUsed')*100.0/size [使用率(%)],max_size/128 [最大值(兆)],case is_percent_growth when 0 then cast(growth/128 as nvarchar) + '兆' else cast(growth as nvarchar) + '%' end 增长值,physical_name 物理路径FROM sys.database_files a ORDER BY a.[name]--创建文件组
ALTER DATABASE ComputingDB_EN ADD FILEGROUP [FG_DailyUsers_2017];ALTER DATABASE ComputingDB_EN ADD FILE (NAME=N'FG_DailyUsers_2017',FILENAME=N'......ndf',SIZE=10MB,MAXSIZE=UNLIMITED,FILEGROWTH=10MB) TO FILEGROUP [FG_DailyUsers_2017];
-- 查看数据库大小
SELECT sum(size/128) [文件(兆)] FROM sys.database_files分区操作
-- 分区记录数select partition_number,rows from sys.partitions where object_id=object_id('xxxx') and index_id in (0,1) order by partition_number;-- 切换分区alter table PczsFunctionLog switch partition 19 to tmp_PczsFunctionLog partition 19;-- 追加分区，只能一个一个加ALTER PARTITION SCHEME [DATE_PS] NEXT USED [DATE_10];ALTER PARTITION FUNCTION DATE_PF() SPLIT RANGE (20141001);-- 合并分区ALTER PARTITION FUNCTION DATE_PF() MERGE RANGE (20141001);文件组操作
-- 生成文件组创建脚本select name,'ALTER DATABASE '+DB_NAME()+' ADD FILEGROUP ['+name+'];ALTER DATABASE '+DB_NAME()+' ADD FILE (NAME=N'''+name+''',FILENAME=N'''+physical_name+''',SIZE=10MB,MAXSIZE=UNLIMITED,FILEGROWTH=10MB) TO FILEGROUP ['+name+'];'from sys.database_files awhere data_space_id>1order by a.name-- 删除文件组drop table dbo.MobileUseLongLog_201305;drop partition scheme day_ps_201305;dbcc shrinkfile ('data_201305',emptyfile);alter database softuselogdb remove file data_201305;alter database softuselogdb remove filegroup data_201305;-- 添加文件组alter database ghw add filegroup [DATA_201207];alter database ghw add file (name='DATA_201207',filename='e:\data\ghw\DATA_201207.ndf',size=10mb,maxsize=unlimited,filegrowth=10mb)to filegroup DATA_201207MySQL相关
-- 查看记录数select table_name,table_rows from information_schema.tables where table_schema='StDB'-- 查看分区数select partition_ordinal_position part,table_rowsfrom information_schema.partitionswhere table_schema = schema() and table_name='func_201506';-- 表结构复制create table if not exists functionlog_201508 like func_201506-- 批量更新update a inner join b on a.id=b.id set a.name=b.name-- 唯一插入insert into a select ?name from dual where not exists(select 1 from a where id=?id);select last_insert_id();-- 先更新后插入update Cfg_PczsVID set name=?name where VID=?vid;insert into Cfg_PczsVID(vid,name)select ?vid,?name from dual where ROW_COUNT()=0;-- 查看进程show processlist-- kill查询，参考KILL [CONNECTION | QUERY] thread_id-- 显示数据库中所有存储的存储过程基本信息，包括所属数据库，存储过程名称，创建时间等show procedure status--查询包含指定内容的存储过程
SELECT obj.[Name] [存储过程名],sc.[TEXT] [存储过程内容]
FROM syscomments sc INNER JOIN sysobjects obj ON sc.ID = obj.ID
WHERE sc.TEXT LIKE '%ResDownloadRank%'
-- 显示某一个存储过程的详细信息show create procedure sp_nameALTER procedure [dbo].[PR_InitTable]asbegin declare @sql nvarchar(max); declare @now datetime=getdate(); declare @date datetime; declare @month nvarchar(6); declare @i int; set @i=0; while @i<3 begin set @date=DATEADD(dd,-7-@i,@now); set @sql = N' if object_id(N''PczsConnectLog_'+ convert(nvarchar,@date,112) +''',N''U'') is not null begin drop table PczsConnectLog_'+ convert(nvarchar,@date,112) +'; end'; exec sp_executesql @sql; set @i=@i+1; end set @i=0; while @i<3 begin set @date=DATEADD(mm,-3-@i,@now); set @month=cast(datepart(yy,@date)*100+datepart(mm,@date) as nvarchar); set @sql = N' if object_id(N''MobileFunctionLog_Sum_'+ cast(@month as nvarchar) +''',N''U'') is not null begin drop table MobileFunctionLog_Sum_'+ cast(@month as nvarchar) +'; end'; exec sp_executesql @sql; set @i=@i+1; end set @i=0; while @i<3 begin set @date=DATEADD(mm,-3-@i,@now); set @month=cast(datepart(yy,@date)*100+datepart(mm,@date) as nvarchar); set @sql = N' if exists (select * from sys.partition_schemes where name = N''PS_MobileFunctionLog_Sum_'+ cast(@month as nvarchar) +''') begin drop partition scheme PS_MobileFunctionLog_Sum_'+ cast(@month as nvarchar) +'; dbcc shrinkfile (''FG_MobileFunctionLog_Sum_'+ cast(@month as nvarchar) +''',emptyfile); alter database ComputingDB_Func remove file FG_MobileFunctionLog_Sum_'+ cast(@month as nvarchar) +'; alter database ComputingDB_Func remove filegroup FG_MobileFunctionLog_Sum_'+ cast(@month as nvarchar) +'; end'; exec sp_executesql @sql; set @i=@i+1; end set @i=0; while @i<3 begin set @date=DATEADD(mm,1+@i,@now); set @month=cast(datepart(yy,@date)*100+datepart(mm,@date) as nvarchar); set @sql = N' if not exists (select * from sys.partition_schemes where name = N''PS_MobileFunctionLog_Sum_'+ cast(@month as nvarchar) +''') begin ALTER DATABASE ComputingDB_Func ADD FILEGROUP [FG_MobileFunctionLog_Sum_'+ cast(@month as nvarchar) +']; ALTER DATABASE ComputingDB_Func ADD FILE ( NAME=N''FG_MobileFunctionLog_Sum_'+ cast(@month as nvarchar) +''', FILENAME=N''f:\MSSQL10.MSSQLSERVER\MSSQL\DATA\ComputingDB_Func\FG_MobileFunctionLog_Sum_'+ cast(@month as nvarchar) +'.ndf'', SIZE=10MB, MAXSIZE=UNLIMITED, FILEGROWTH=10MB ) TO FILEGROUP [FG_MobileFunctionLog_Sum_'+ cast(@month as nvarchar) +']; create partition scheme [PS_MobileFunctionLog_Sum_'+ cast(@month as nvarchar) +'] as partition [PF_Part] all to ([FG_MobileFunctionLog_Sum_'+ cast(@month as nvarchar) +']); end'; exec sp_executesql @sql; set @sql = N' if object_id(N''MobileFunctionLog_Sum_'+ cast(@month as nvarchar) +''',N''U'') is null begin CREATE TABLE MobileFunctionLog_Sum_'+ cast(@month as nvarchar) +'( [SoftID] [int] NOT NULL, [Platform] [tinyint] NOT NULL, [VersionID] [int] NOT NULL, [FunctionID] [int] NOT NULL, [FunctionLabel] [nvarchar](100) NULL, [IMEI] [varchar](100) NOT NULL, [Part] [tinyint] NOT NULL, [OptionDate] [int] NOT NULL, [OptionHour] [int] NOT NULL, [OptionTimes] [int] NOT NULL, [NetMode] [tinyint] NOT NULL, [Jailbroken] [tinyint] NOT NULL, [IP] [bigint] NOT NULL, [HashCode] [int] NOT NULL, [ParamTagID] [int] NULL ) ON [PS_MobileFunctionLog_Sum_'+ cast(@month as nvarchar) +']([Part]); CREATE CLUSTERED INDEX [IX_Date] ON [MobileFunctionLog_Sum_'+ cast(@month as nvarchar) +'] ([OptionDate] ASC); end'; exec sp_executesql @sql; set @i=@i+1; endend