如何在所有存储过程中获取特定表的列表列？ [英] How can I get list column used of specific table in among all stored procedure?

问题描述

如何在所有存储过程中使用特定表的列列表？



假设，



我有一个表（VendorMaster），它有100列，我想知道在所有存储过程中使用了多少列。

解决方案

是。 ..的分析。

您可以获取使用特定列的过程的名称： http://stackoverflow.com/questions/19539696/find-all-stored-procedures-that-reference-a-column-in-table-sometable [ ^ ]并且可以自动化返回所有这些。



但是......它会抓住吗？

  SELECT  *  FROM  VendorMaster 

编号并且这并未引用随后实际使用。

以下是此解决方案： -



我已通过此查询完成...

  IF   OBJECT_ID （'  tempdb.dbo。＃SPDependencyDetails'） IS  < span class =code-keyword> NOT   NULL  
  DROP   TABLE  #SPDependencyDetails 
 
  CREATE   TABLE  #SPDependencyDetails 
（
 Or_Object_Database  NVARCHAR （ 128 ）
，Or_Object_Name  NVARCHAR （ 128 ）
，Ref_Database_Name  NVARCHAR （ 128 ）
，Ref_Schema_Name  NVARCHAR （ 128 ）
，Ref_Object_Name  NVARCHAR （ 128 ）
，Ref_Column_Name  NVARCHAR  （ 128 ）
，Is_Selected  BIT  
，Is_Updated  BIT  
，Is_Select_All  BIT  
，Is_All_Columns_Found  BIT  
）
 
  DECLARE   @ database_name   VARCHAR （ 100 ）
 
  DECLARE  database_cursor  CURSOR  
  FOR  
  SELECT  name 
  FROM  sys .databases 
  WHERE  database_id = 8 
 
  OPEN  database_cursor 
 
  FETCH  NEXT  FROM  database_cursor 
  INTO   @ database_name  
 
  WHILE   @@ FETCH_STATUS  =  0    -   外循环开始 
  BEGIN  
  DECLARE   @ WholeLotofSQL   NVARCHAR （MAX）= '  
 DECLARE @object_name VARCH AR（150）
，@ sqlstatement NVARCHAR（2500）
 
 DECLARE object_cursor CURSOR  - 内部游标，迭代匹配类型为
的对象列表FOR 
 SELECT name 
 FROM' + @ database_name + ' 。sys.objects AS o 
 WHERE o。 type =''P'' - 更改对象类型以查找函数，视图等的依赖项。
 ORDER BY 1 
 
 OPEN object_cursor 
 FETCH NEXT FROM object_cursor INTO @object_name 
 
 WHILE @@ FETCH_STATUS = 0  - 内部循环开始
 BEGIN 
 SET @sqlstatement =''USE' + @ database_name + ' ; 
 
 INSERT INTO #SPDependencyDetails 
 SELECT DB_NAME（）AS Or_Object_Database 
，''''''+ @object_name +''''''AS Or_Object_Name 
，例如，references_database_name为空，那么DB_NAME（）
 ELSE referenced_database_name 
 END AS Ref_Database_Name 
，referenced_schema_name AS Ref_Schema_Name 
，referenced_entity_name AS Ref_Object_Name 
，referenced_minor_name AS Ref_Column_Name 
 ，is_selected 
，is_updated 
，is_select_all 
，is_all_colum ns_found 
 FROM sys.dm_sql_referenced_entities（''''dbo。''+ @object_name +'''''''''''OBJECT''''）'''
 
 EXEC sys.sp_executesql @sqlstatement 
 
 FETCH NEXT FROM object_cursor INTO @object_name 
 END 
 CLOSE object_cursor 
 DEALLOCATE object_cursor' 
 
  EXEC  sys.sp_executesql  @ WholeLotofSQL  
 
  FETCH  NEXT  FROM  database_cursor  INTO   @database_name  
  END  
 
  CLOSE  database_cursor; 
  DEALLOCATE  database_cursor; 
 
  SELECT  Or_Object_Database  as  '  数据库' 
，Or_Object_Name  as  '  Procedure' 
，Ref_Object_Name  as  ' 表' 
，Ref_Column_Name  as  ' 列' 
  FROM  #SPDependencyDetails 

How can I get used column list of a specific table in among all stored procedure?

Suppose that,

I have a table(VendorMaster) which has 100 columns just I want to know how many columns used in among all stored procedure.

解决方案

Yes...sort of.
You can get the names of procedures that use a specific column: http://stackoverflow.com/questions/19539696/find-all-stored-procedures-that-reference-a-column-in-table-sometable[^] and it shoudl be possible to "automate" that to return all of them.

But...Would it catch

SELECT * FROM VendorMaster

No. And that doesn't reference how many columns ot that are subsequently actually used.

Here is solution for this:-

I have done by this query...

IF OBJECT_ID('tempdb.dbo.#SPDependencyDetails') IS NOT NULL
DROP TABLE #SPDependencyDetails

CREATE TABLE #SPDependencyDetails
(
 Or_Object_Database NVARCHAR(128)
,Or_Object_Name NVARCHAR(128)
,Ref_Database_Name NVARCHAR(128)
,Ref_Schema_Name NVARCHAR(128)
,Ref_Object_Name NVARCHAR(128)
,Ref_Column_Name NVARCHAR(128)
,Is_Selected BIT
,Is_Updated BIT
,Is_Select_All BIT
,Is_All_Columns_Found BIT
)

DECLARE @database_name VARCHAR(100)

DECLARE database_cursor CURSOR
FOR
SELECT name
    FROM sys.databases
    WHERE database_id =8

OPEN database_cursor

FETCH NEXT FROM database_cursor
INTO @database_name

WHILE @@FETCH_STATUS = 0 --Outer Loop begin
BEGIN
    DECLARE  @WholeLotofSQL NVARCHAR(MAX) =       '
    DECLARE @object_name VARCHAR(150)
    ,@sqlstatement NVARCHAR(2500)

    DECLARE object_cursor CURSOR --Inner cursor, iterates list of objects that match type
    FOR
        SELECT name
            FROM '+@database_name+'.sys.objects AS o
            WHERE o.type = ''P'' --Change Object type to find dependencies of Functions, Views and etc.
            ORDER BY 1    

    OPEN object_cursor
    FETCH NEXT FROM object_cursor INTO @object_name

    WHILE @@FETCH_STATUS = 0  --Inner Loop Begin
        BEGIN
            SET @sqlstatement = ''USE '+@database_name+';

                                INSERT INTO #SPDependencyDetails
                                SELECT DB_NAME() AS Or_Object_Database
                                        ,'''''' + @object_name + '''''' AS Or_Object_Name
                                        ,CASE WHEN referenced_database_name IS NULL THEN DB_NAME()
                                                ELSE referenced_database_name
                                        END AS Ref_Database_Name
                                        ,referenced_schema_name AS Ref_Schema_Name
                                        ,referenced_entity_name AS Ref_Object_Name
                                        ,referenced_minor_name AS Ref_Column_Name
                                        ,is_selected
                                        ,is_updated
                                        ,is_select_all
                                        ,is_all_columns_found
                                    FROM sys.dm_sql_referenced_entities(''''dbo.'' + @object_name + '''''', ''''OBJECT'''');''

            EXEC sys.sp_executesql @sqlstatement

            FETCH NEXT FROM object_cursor INTO @object_name
        END      
    CLOSE object_cursor
    DEALLOCATE object_cursor'

    EXEC sys.sp_executesql @WholeLotofSQL

    FETCH NEXT FROM database_cursor INTO @database_name
END

CLOSE database_cursor;
DEALLOCATE database_cursor;

SELECT Or_Object_Database as 'Database'
,Or_Object_Name as 'Procedure'
,Ref_Object_Name as 'Table'
,Ref_Column_Name as 'Column'
FROM #SPDependencyDetails

这篇关于如何在所有存储过程中获取特定表的列表列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！