sqlServer实例下寻找字段名

最新推荐文章于 2019-06-24 16:37:48 发布

弘恩

最新推荐文章于 2019-06-24 16:37:48 发布

阅读量283

点赞数

本文链接：https://blog.csdn.net/c_enhui/article/details/51153629

版权

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[sp_get_table_max]    Script Date: 2016/4/14 17:25:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
drop PROCEDURE [dbo].[sp_get_column] 
go
create PROCEDURE [dbo].[sp_get_column] ( @col nvarchar(128),@db NVARCHAR(128) = '' ,@tb NVARCHAR(128) = ''  )       
AS    
    
SET NOCOUNT ON ;    
--  exec     master.[dbo].[sp_get_column] 'FreezeMoney' 
 --
CREATE TABLE #TableSpace        
    ( DB_NAME VARCHAR(128) DEFAULT ( db_name()),    
      ObjectName VARCHAR(128) ,    
      TypeName  VARCHAR(128) )    
    
DECLARE @sql NVARCHAR(max)     
SET @sql = ''    
SELECT @sql = @sql + REPLACE (    
'    
USE [@dbname]     
INSERT INTO #TableSpace (  ObjectName ,TypeName  )       
select object_name(a.object_id) as  ObjectName ,b.type
from  sys.all_columns a
join  sys.all_objects b on a.object_id = b.object_id 
where a.name like ''%'+  @col + '%''  
 ' ,'@dbname',name)      
FROM sys.databases     
WHERE name LIKE @db + '%' AND database_id >= 5    
PRINT @sql    
EXEC (@sql)    
    
SELECT  ' SELECT TOP 10 * FROM ['+ DB_NAME +']..['+ObjectName+ ']' AS SQL ,*     
FROM #TableSpace   
DROP TABLE  [#TableSpace]    
    
go 

EXEC sp_MS_marksystemobject 'sp_get_column'    
go