–查找表约束
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’;
–对象的取得所有权与控制的权限区别
–经测试：取得所有权的权限可以deny,而控制权限忽略deny
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)
/*在修改列类型时，提示有依赖，如下。现提供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>
在远程登录Windows server 2003的时候，会遇到这样的情况：按下L键就等于按下win+L，结果远程服务器 的屏幕就锁定了；按下D就等于按下win+D就显示远程服务器桌机了；按下F键就自动调出搜索界面……
解决方法：
第一种方法：按住win键同时连按三次“alt”键，问题也许解决。
第二种方法：在“运行”中输入osk打开windows自带的软键盘，然后鼠标点击软键盘上的win键，关闭软键盘
利用：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