2012年2月 的存档
2012二月27

查找表约束

sql server 评论关闭

–查找表约束
SELECT OBJECT_NAME(c.object_id),c.name,d.name
FROM sys.default_constraints d
JOIN sys.columns c ON d.parent_column_id = c.column_id AND d.parent_object_id = c.object_id
WHERE d.parent_object_id = OBJECT_ID(N’bdAllocateGroup’) –AND c.name = ‘msrepl_tran_version’;

2012二月25

用户权限查询

sql server 评论关闭
–开连接
CREATE USER [zinnova] FOR LOGIN [zinnova]
GO
 
–开表
GRANT SELECT ON OBJECT::dbo.mdOrderStatus TO zinnova;
GO
 
–查看 SQL 2005 用户被赋予的权限
exec sp_helprotect @username = 'zinnova'
 
–查看用户角色
select DbRole = g.name, MemberName = u.name, MemberSID = u.sid
from sys.database_principals u, sys.database_principals g, sys.database_role_members m
where g.principal_id = m.role_principal_id
and u.principal_id = m.member_principal_id
order by 1, 2

–对象的取得所有权与控制的权限区别
–经测试:取得所有权的权限可以deny,而控制权限忽略deny

2012二月24

guid做主键选择char(36)还是Uniqueidentifier

sql server 评论关闭

1,存储空间
char(36):存储空间是36个字节,
Uniqueidentifier是16个字节,原因(如:F9274DE1-322D-46FF-A5E9-20670FFF661B,每个都是16进制,共有32个,而每个16进制数可用4位表示(2^4),故guid()是128位,一个字节是8位,那么guid()共用128/8=16个字节存储):

2,Uniqueidentifier格式必须是32个16进制+4个-,那么位数少于36或者字母中出现非a-f就会报错,如
0EC75E6B-7434-485E-BCEA-2130651861DD可用Uniqueidentifier存储,但0HC75E6B-7434-485E-BCEA-2130651861DD就不行。
故在兼容性方面,char(36)可存储<=36的所有字符,而Uniqueidentifier只能存储32个16进制+4个-.

故:在现在存储空间价格便宜的情况下,在兼容方面,推荐char(36)

2012二月23

修改列类型复制索引

sql server 评论关闭

/*在修改列类型时,提示有依赖,如下。现提供AlterColumnType存储过程用来复制表依赖关系。
ALTER TABLE dbo.bmdCustomerTel ALTER COLUMN CustomerID INT
消息 5074,级别 16,状态 1,第 1 行
索引'IX_bmdCustomerTel_2' 依赖于 列'CustomerID'。
消息 4922,级别 16,状态 9,第 1 行
由于一个或多个对象访问此列,ALTER TABLE ALTER COLUMN CustomerID 失败。
*
--主键:SELECT TABLE_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME='表名'

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AlterColumnType]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[AlterColumnType]
GO

/*修改列类型时,自动查找相关索引,删除再重建*/
CREATE PROCEDURE [dbo].[AlterColumnType]
@TableName VARCHAR(50),
@ColumnName VARCHAR(50),
@NewColumnType VARCHAR(1000)
AS

SELECT t.object_id,ix.type_desc,t.name TableName,IX.index_id,ix.name indexName,s.name SchemeName INTO #
FROM SYS.tables t
JOIN SYS.indexes IX ON t.[object_id] = IX.[object_id]
JOIN SYS.schemas s ON S.[schema_id] = t.[schema_id] --表架构名
WHERE t.object_id = object_id(@TableName)
AND EXISTS( --拥有此列的索引
SELECT 1 FROM sys.index_columns cl
JOIN sys.columns c ON cl.column_id = c.column_id AND c.object_id = cl.object_id
WHERE c.name = @ColumnName AND cl.index_id = ix.index_id AND cl.object_id = t.object_id )

DECLARE cur_dx CURSOR FOR
SELECT ' DROP INDEX ' + QUOTENAME(indexName) + ' ON ' + QUOTENAME(SchemeName) + '.' + QUOTENAME(TableName) --t.name,ix.name,ix.type_desc,cl.name
FROM # t

UNION ALL

SELECT ' ALTER TABLE ' + @TableName + ' ALTER COLUMN '+ @ColumnName + ' ' + @NewColumnType

UNION ALL

SELECT ' CREATE ' + type_desc collate Chinese_PRC_CI_AS_WS + ' INDEX ' + QUOTENAME(indexName) + ' ON ' + QUOTENAME(SchemeName) + '.' + QUOTENAME(TableName)
+ '(' + STUFF( --索引列,复合索引
(SELECT ',' + CONVERT(NVARCHAR(100),cl.name)
FROM sys.index_columns c
JOIN sys.columns cl ON cl.column_id = c.column_id AND c.object_id = cl.object_id
WHERE c.object_id = t.object_id AND index_id = t.index_id
ORDER BY key_ordinal
FOR XML PATH('')),
1,1,'') + ')'
FROM # t

DECLARE @sql NVARCHAR(max)
OPEN cur_dx
FETCH cur_dx INTO @sql
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_executesql @sql
RAISERROR('Executed:%s',10,1,@sql) WITH NOWAIT
--PRINT @SQL
FETCH cur_dx INTO @sql
END
CLOSE cur_dx
DEALLOCATE cur_dx

DROP TABLE #

/pre>

2012二月10

远程桌面连接时总是会自动按下win键

windows server 评论关闭

在远程登录Windows server 2003的时候,会遇到这样的情况:按下L键就等于按下win+L,结果远程服务器 的屏幕就锁定了;按下D就等于按下win+D就显示远程服务器桌机了;按下F键就自动调出搜索界面……

解决方法:
第一种方法:按住win键同时连按三次“alt”键,问题也许解决。

第二种方法:在“运行”中输入osk打开windows自带的软键盘,然后鼠标点击软键盘上的win键,关闭软键盘

2012二月8

多服务器mssql job运行监控

mysql 评论关闭

利用:1,mssql job运行。2,链接服务器,3,mssql 邮件发送

例:

DECLARE @LastRun VARCHAR(10)

SELECT @SyncDate = SyncDate FROM dbo.SyncTable

WHERE TableName = ‘JobLogMonitor’

IF @@ROWCOUNT = 0 BEGIN RETURN END

SET @LastRun = CONVERT(VARCHAR(10),@SyncDate,112)

DECLARE @Receivers varchar(1000)

SET @receivers = ‘wukun@lvshou.com;liqihua@lvshou.com;zhengxuesong@lvshou.com’

DECLARE @table VARCHAR(max),@body VARCHAR(max),@COUNT INT

SET @COUNT = 0 SET @SyncCount = 0

–2.201

SET @table = ‘



SELECT @table = @table + ‘

‘+ ‘

‘+ ‘

‘+ ‘



FROM server.msdb.dbo.sysjobhistory sjh(NOLOCK)

JOIN server.msdb.dbo.sysjobs sj(NOLOCK) ON sjh.job_id = sj.job_id

JOIN server.msdb.dbo.sysjobsteps sjt(NOLOCK) ON sjh.step_id = sjt.step_id AND sjh.job_id = sjt.job_id

WHERE run_status =0

AND msdb.dbo.agent_datetime(run_date,run_time)> @LastRun

ORDER BY run_date

SET @COUNT = @@ROWCOUNT

IF @COUNT > 0

BEGIN

SET @SyncCount = @SyncCount + @COUNT

SET @table = @table + ‘

‘ + CONVERT(VARCHAR(10),ROW_NUMBER() OVER (ORDER BY run_date))+ ‘ job名称:’ + CASE(ISNULL(sj.NAME,”))WHEN ” THEN ‘ ’ ELSE sj.NAME END +

步骤:’ + CASE(ISNULL(sjt.step_name,”))WHEN ” THEN ‘ ’ ELSE sjt.step_name END + REPLICATE(‘ ’,4) + ‘执行时间:’ + CASE(ISNULL(sjh.run_date,”))WHEN ” THEN ‘ ’ ELSE CONVERT(VARCHAR(50),sjh.run_date,120) END +

错误消息:’ + CASE(ISNULL(sjh.message,”))WHEN ” THEN ‘ ’ ELSE sjh.message END +

执行SQL:’ + CASE(ISNULL(sjt.command,”))WHEN ” THEN ‘ ’ ELSE sjt.command END +



SET @body = dbo.getHTML(@table)

–SELECT @body

EXEC msdb.dbo.sp_send_dbmail

@profile_name = ‘LogMonitor’,

@recipients = @receivers,

@body_format = ‘HTML’,

@body = @body,

@subject = ‘Job执行错误[201]‘;

END