–查找表约束
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’;

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>