存储过程查询

查询数据库中存储过程

 

SELECT name, definition
FROM sys.sql_modules AS m
INNER JOIN sys.all_objects AS o ON m.object_id = o.object_id
WHERE o.[type] = 'P'

 

通过存储过程查找某字段存在哪一张表中的哪一列存在

 

CREATE PROCEDURE [dbo].[SP_FindValueInDB]   
(    
@value VARCHAR(1024)    
)         
AS    
BEGIN    
-- SET NOCOUNT ON added to prevent extra result sets from    
-- interfering with SELECT statements.    
SET NOCOUNT ON;    
DECLARE @sql VARCHAR(1024)  
DECLARE @table VARCHAR(64)  
DECLARE @column VARCHAR(64)  
CREATE TABLE #t (  
    tablename VARCHAR(64),  
    columnname VARCHAR(64)  
)  
DECLARE TABLES CURSOR    
FOR    
SELECT o.name, c.name  
FROM syscolumns c  
INNER JOIN sysobjects o ON c.id = o.id  
WHERE o.type = 'U' AND c.xtype IN (167, 175, 231, 239)  
ORDER BY o.name, c.name  
OPEN TABLES  
FETCH NEXT FROM TABLES  
INTO @table, @column    
WHILE @@FETCH_STATUS = 0    
BEGIN    
SET @sql = 'IF EXISTS(SELECT NULL FROM [' + @table + '] '    
SET @sql = @sql + 'WHERE RTRIM(LTRIM([' + @column + '])) LIKE ''%' + @value + '%'') '    
SET @sql = @sql + 'INSERT INTO #t VALUES (''' + @table + ''', '''    
SET @sql = @sql + @column + ''')'    
EXEC(@sql)  
FETCH NEXT FROM TABLES  
INTO @table, @column    
END    
CLOSE TABLES  
DEALLOCATE TABLES  
SELECT *    
FROM #t  
DROP TABLE #t  
End

本文整理自网络！

转载于:https://www.cnblogs.com/YuanSong/archive/2013/04/10/3012079.html