ms sql server 日常sql记录

----------------------------------查询数据库端口-----------------------------------------------

--查询数据库端口
exec sys.sp_readerrorlog 0, 1, 'listening'

----------------------------------查看当前数据库的连接数------------------------------------------------

--exec sp_who '登陆用户'
 select client_net_address,COUNT(0) AS CL
 from sys.dm_exec_connections
 where net_transport = 'TCP' --and client_net_address='113.31.16.195'
 group by client_net_address
 order by CL DESC

----------------------------------还原为只读数据库------------------------------------------------

RESTORE DATABASE [bbbb] FROM  DISK = N'D:\aaa.bak' WITH  FILE = 1,  STANDBY = N'D:\SQL2K8_DATA\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\ROLLBACK_UNDO_bbbb.BAK',  NOUNLOAD,  STATS = 10
GO


这样执行出来就是Standby/Read-Only状态。
可以只读。
读完之后就可以继续使用后面的备份文件继续恢复。

----------------------------------生成随机数-------------------------------------------------

SELECT CRYPT_GEN_RANDOM(50) ;

SELECT Convert(int,CRYPT_GEN_RANDOM(50)) ;

SELECT CRYPT_GEN_RANDOM(4, 0x25F18060) ;

SELECT Convert(int,CRYPT_GEN_RANDOM(4, 0x25F18060))

 

----------------------------------统计表的统计信息，生成统计语句-------------------------------------------------

 

select 'CREATE STATISTICS '+R.name+N' on [dbo].[Orders]('+stuff((
select ','+C.name
from sys.stats A,sys.stats_columns B,sys.columns C
where  A.object_id = B.object_id
 and A.object_id = C.object_id
 and B.stats_column_id = C.column_id
 and A.stats_id = B.stats_id
 and A.object_id = R.object_id
 and A.stats_id = R.stats_id
for xml path('') 
),1,1,N'')+N')'
from sys.stats R
where R.object_id = OBJECT_ID('Orders_bak2013')
and not exists
(
 select 1 from sys.stats where object_id=OBJECT_ID('Orders') and name = R.name
)

 

 

----------------------------------根据当前时间生成月份列表-------------------------------------------------

 With GetDateM
AS
(
 Select Convert(nvarchar(7),DateAdd(MM,-12,GETDATE()),120) AS DateM,DDT=-11
 Union all
 Select Convert(nvarchar(7),DateAdd(MM,DDT,GETDATE()),120) AS DateM,DDT=B.DDT+1
 From GetDateM B
 Where B.DDT <=2
)
select * from GetDateM order by DateM

 

----------------------------------SQL读取文件示例-------------------------------------------------

create table cmd (a text);
BULK INSERT cmd
FROM 'F:\XXX-XXX.sql'
WITH (
FIELDTERMINATOR ='|',
ROWTERMINATOR = '\n',
FIRE_TRIGGERS
)

select * from cmd

truncate table cmd

-----------------清除发布环境-------------------------------

 

sp_replcounters

sp_removedbreplication @dbname = N'XXX',@type = N'both'

sp_removedistpublisherdbreplication @publisher = 'XXX\XXX26'
        , @publisher_db = N'XXX'
 
sp_replmonitorhelppublisher

select @@SERVERNAME

 

-----------------清除发布环境-------------------------------

--查询磁盘剩余空间

master.dbo.xp_fixeddrives

-----------------清除缓存-------------------------------

-- 首先清除缓存  
DBCC DROPCLEANBUFFERS;  
DBCC FREEPROCCACHE; 

---------------------------dcpromo------------------------------------

dcpromo

域控制器的运行命令

 

disk.locking = "false"
diskLib.dataCacheMaxSize = "0"
scsi1.present = "TRUE"
scsi1.virtualDev = "lsilogic"
scsi1:5.present = "TRUE"
scsi1:5.fileName = "D:\Virtual Machines\ShareDisks\Quorum.vmdk"
scsi1:6.present = "TRUE"
scsi1:6.fileName = "D:\Virtual Machines\ShareDisks\ShareDisk.vmdk"

 

 

---------------------MS SQL Server 字段映射-------------------------------------

select * from msdb.dbo.msdatatype_mappings

 

---------------------MS SQL Server 区分大小写-------------------------------------

select [区分大小写] =
  Case when 'A' Collate Chinese_Prc_90_CS_AS = 'A'
   then 1
   else 0
   end

--------------MS SQL Server 搜索文件---------------------

DBCC SHRINKFILE (N'逻辑文件名' , 153643)

----------------MS SQL Server 分配权限----------------------

EXEC sp_helprotect @name ='XXX',@username = 'XXX'

eg:

Create table #
(
 Owner nvarchar(100),
 Object nvarchar(100),
 grantee nvarchar(100),
 grantor nvarchar(100),
 wew nvarchar(100),
 actions nvarchar(100),
 Columnse nvarchar(100)
)
insert #
EXEC sp_helprotect NULL, 'mobile'

select N'Grant '+actions+' on '+Object +N' to mobile'
from #

 

-------------------------数据用户映射----------------------------------------------

USE [UserTempDB]
GO
CREATE USER [Robot] FOR LOGIN [Robot]
GO
USE [UserTempDB]
GO
EXEC sp_addrolemember N'db_owner', N'Robot'
GO

---------------------------SQL Server缓存执行计划的查询----------------------------------------- 

select c.usecounts,c.cacheobjtype,c.objtype,t.text
from sys.dm_exec_cached_plans c cross apply sys.dm_exec_sql_text(c.plan_handle) t
where t.text like N'SELECT TOP 300 %'

---------------------------SQL Server自带的密码加密和加密匹配函数----------------------------------------- 

select top 1 PWDENCRYPT(name),pwdcompare('XXX',PWDENCRYPT(name)) as N'匹配'
from sys.objects

 ---------------------------查找删除统计-----------------------------------------

 

select 'drop STATISTICS ' + object_name(id) + '.' + name
from sysindexes
where name like 'Statistic_%';

 

--------------查找表的外键；-------------------------

 

select 'Alter table '+Object_name(Parent_Object_id)+ ' drop Constraint '+name from sys.foreign_keys

---------------查找表的默认值---------------------------------

select 'alter table ' + OBJECT_NAME(df.parent_object_id) + ' add constraint ' + df.name + ' default ' + df.definition + ' for ' + COL_NAME(df.parent_object_id,df.parent_column_id)
from sys.default_constraints as df, sysobjects as o
where df.parent_object_id = o.id
 and o.xtype = 'U'
order by o.name

 

--统计执行时间

set statistics profile on
set statistics io on
set statistics time on
go
<这里写上你的语句...>
go
set statistics profile off
set statistics io off
set statistics time off

For example:

SET STATISTICS io ON
SET STATISTICS time ON
go
SELECT *  FROM TBL_MeetingRoomOrder
go
SET STATISTICS profile OFF
SET STATISTICS io OFF
SET STATISTICS time OFF

 

 

 

----数据库批量赋权语句

Declare @DynSql  nvarchar(2000),
 @ObjName  nvarchar(128),
 @RowCount  int,
 @i   int,
 @LoginName  nvarchar(20)

SET NOCOUNT ON
---------------------------------------------------------
Create Table #Objects
(
 SeqID_int   int IDENTITY(1,1),
 ObjectName_nvarchar  nvarchar(128)
)

set @LoginName = 'B2C'
Insert Into #Objects
 Select name From sysobjects Where type in ('U', 'V', 'TF' ) and status >= 0;

Set @RowCount = @@ROWCOUNT
Set @i = 1

WHILE(@i <= @RowCount)
Begin
 Select @ObjName = ObjectName_nvarchar
 From #Objects
 Where SeqID_int = @i;
 
 Set @DynSql = 'grant select on [' + @ObjName + '] TO [' + @LoginName + ']';
 EXEC (@DynSql)
 
 Set @i = @i + 1
End

Truncate Table #Objects;
Drop Table #Objects;

SET NOCOUNT OFF

 

 

------------------------收回系统表的查询权限提高安全性-------------------------------------------

 

revoke select on sys.all_columns from public
revoke select on sys.all_objects from public
revoke select on sys.columns from public
revoke select on sys.objects from public
revoke select on sys.syscolumns from public
revoke select on sys.sysobjects from public
revoke select on sys.tables  from public

 

------------------------查看备份的记录-------------------------------------------

 

use msdb
select backup_start_date,backup_finish_date,type from backupset where database_name = 'SourceDB'--'（要查看的数据库）'

来查看备份的记录，其中
D:代表完全备份
I：代表差异备份
L：代表事物日志备份

 

------------------------添加错误消息-------------------------------------------

sp_addmessage @msgnum = 50001,@severity = 16,@msgtext = N'员工代码没有找到';
GO
--必须添加此消息的 us_english 版本后，才能添加 '简体中文' 版本。

 
EXEC sp_addmessage 50001, 16, 'Member number not found.','us_english',false,replace
EXEC sp_addmessage 50001, 16, '员工代码没有找到','简体中文',false,replace

 

RAISERROR (50001, -- Message id.
           16, -- Severity,
           1, -- State,
           N'abcde'); -- First argument supplies the string.
-- The message text returned is: <<    abc>>.
GO

 

 

-- SQL使用内存信息

SELECT * FROM sys.dm_os_performance_counters
WHERE counter_name IN ('Target Server Memory (KB)','Total Server Memory (KB)')
 

select SUM(single_pages_kb) as SumPagesKb,type
from sys.dm_os_memory_clerks
group by type
order by SumPagesKb desc

select ISNULL(DB_Name(database_id),'ResourceDB') As DBName,
  Cast(COUNT(dobd.row_count) * 8.0/1024 as Decimal(10,2)) AS [Size(M)]
from sys.dm_os_buffer_descriptors dobd
group by database_id
order by DBName

select SUM(SumCnt)
From (
select Sum(SumSingleKb)+Sum(SumMultiKb) SumCnt
from (
select SUM(domc.single_pages_kb) SumSingleKb,SUM(domc.multi_pages_kb) as SumMultiKb
from sys.dm_os_memory_clerks domc
group by type
) a
union
select COUNT(dobd.row_count) * 8.0  AS SumCnt
from sys.dm_os_buffer_descriptors dobd
) b

 
DBCC MemoryStatus
 

--查看服务器配置
 
SELECT
cfg.name AS [Name],
cfg.configuration_id AS [Number],
cfg.minimum AS [Minimum],
cfg.maximum AS [Maximum],
cfg.is_dynamic AS [Dynamic],
cfg.is_advanced AS [Advanced],
cfg.value AS [ConfigValue],
cfg.value_in_use AS [RunValue],
cfg.description AS [Description]
FROM
sys.configurations AS cfg

 

 

--通过以下方式可以解决该问题：

--步骤1：

exec sp_change_users_login 'REPORT'

EXEC sp_change_users_login 'Report';

--列出当前数据库的孤立用户

--步骤2：

exec sp_change_users_login 'AUTO_FIX','用户名'

--可以自动将用户名所对应的同名登录添加到syslogins中

--步骤3：

exec sp_change_users_login 'UPDATE_ONE','用户名','登录名'

将用户名映射为指定的登录名。

 

 --End