1. 方法一:
/************************************************************
* Code formatted by SoftTree SQL Assistant ?v6.0.70
* Time: 2012/11/21 23:13:33
************************************************************/
SET NOCOUNT ON
DECLARE @T_objs TABLE
(objname VARCHAR(1000))
DECLARE @T_objText TABLE
(objname VARCHAR(MAX))
DECLARE @T_result TABLE
(objname VARCHAR(MAX))
INSERT INTO @T_objs
SELECT b.[name] + '.' + a.[name]
FROM sysobjects a
JOIN sys.schemas b
ON a.[uid] = b.[schema_id]
WHERE b.name = 'ILG'
AND (xtype = 'P' OR xtype = 'fn' OR xtype = 'v')
--insert into @T_objs select 'ILG.'+[name] from sysobjects where UID=6 AND (xtype='P' OR xtype='fn' OR xtype='v')
DECLARE @name NVARCHAR(1000)
DECLARE @sqlText VARCHAR(MAX)
SET @sqlText = ''
SET @name = ''
SELECT TOP 1 @name = objname
FROM @T_objs
WHILE @@rowcount > 0
BEGIN
INSERT @T_objText
EXEC sp_helpText @name
SELECT @sqlText = @sqlText + objname
FROM @T_objText
DELETE @T_objText
INSERT INTO @T_objText
SELECT REPLACE(
REPLACE(REPLACE(@sqlText, CHAR(13) + CHAR(10), ''), '', ''),
'',
''
)
--print replace(replace(replace(@sqlText,char(13)+char(10),''),'',''),'','')
IF EXISTS(
SELECT *
FROM @T_objText
WHERE objname LIKE '%TableName%'
) --多条件控制
BEGIN
INSERT INTO @T_result
SELECT @name
END
SET @sqlText = ''
DELETE @T_objs
WHERE @name = objname
DELETE @T_objText
SELECT TOP 1 @name = objname
FROM @T_objs
END
SELECT *
FROM @T_result
2. 方法二:
SELECT *
FROM sysobjects s
INNER JOIN syscomments s2
ON s.id = s2.id
WHERE s2.text LIKE '%TableName%'
将TableName替换成想要检查的表名即可。