CODEWITHSUNDEEP
sql-serverreporting-services
Ash Atre
Ash Atre

Reputation: 305

How to get list of tables used in complex SQL Query

I queried the SSRS Database catalog and created small table which has the sql statements used in different SSRS report. I would like to find out the table names mentioned in those SQL Statement. Since SQL Statmenets are directly imported from SSRS, it expects some parameters for their execution. So, there is no option of executing these queries and finding the table information from SQL Server Metadata objects.

In all, I would like to know if there is any SQL way to do string parsing and pattern matching to find keywords : FROM, JOIN, APPLY. Then search for a space (skipping the one after the key word). Anything between the spaces is a table or table function. the query can also take into consideration FROM with commas afterwards, this would be an older way of joining tables.

Below is the data set for reference

enter image description here

Upvotes: 0

Views: 467

Answers (1)

Vijesh V
Vijesh V

Reputation: 11

Please use following script to get objects used in a procedure.

DECLARE
@SearchKeyWord VARCHAR(MAX) = 'rpt_gl_summary_report'   --//Procedure Name

DECLARE
@ObjectID VARCHAR(MAX) = '',
@Cntr INT = 1,
@Cnt INT,
@ObjectName VARCHAR(MAX) = '',
@TableName VARCHAR(MAX) = '',
@ID VARCHAR(MAX) = ''

SELECT
  @ObjectID = ID
FROM SYSOBJECTS
WHERE NAME LIKE '%' + @SearchKeyWord + '%'

DECLARE @SysTables TABLE
(
  ID INT IDENTITY PRIMARY KEY,
  ObjectName VARCHAR(MAX)
)


INSERT INTO @SysTables
(
  ObjectName
)
SELECT
   TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES

SET @Cnt = @@ROWCOUNT

WHILE @Cntr <= @Cnt
BEGIN
   SET @ID = ''

   SELECT
      @ObjectName = ObjectName
   FROM @SysTables
   WHERE ID = @Cntr


;WITH CTE_List
AS
(
    SELECT
        REPLACE(REPLACE(text, '[', ''), ']', '') AS text, id
    FROM SYSCOMMENTS
    WHERE ID = @ObjectID
)
    SELECT
        @ID = id
    FROM CTE_List
    WHERE text LIKE '%' + @ObjectName + '%'

IF ISNULL(@ID, '') <> ''
BEGIN
    PRINT @ObjectName
    SET @TableName = @TableName + CASE WHEN @TableName <> '' THEN ' - ' ELSE '' END + @ObjectName
END

SET @Cntr = @Cntr + 1
END

SELECT
   @ObjectID 'ObjectID', @SearchKeyWord 'Procedure Name', @TableName 'TableName'

Upvotes: 1

Related Questions