T-sql – Need to get identify all tables and columns in a SQL Query

scriptingt-sql

I have a massive task to go through hundreds of SQL scripts and identify the Table and Columns in each query as we are updating our system and some of the columns and tables may be renamed or disappear completely.

Is there any product of script that anyone can recommend that will allow me to do this, taking into consideration that whatever program/script is used will need to take into account alias as well as ensuring that it can differentiate between blocks of select code or union.

I have hundreds of queries being used for reports. I need to go through each query and identify tables and columns that are being used within each query.

Best Answer

If the queries are in stored procedures, or you can get them into a temporary database as stored procedures then the system SP sp_depends and related catalog views will help. At the very least they will narrow the search.

If the queries are in files a bit of Powershell will generate SPs in no time.

If they're embedded in the application or, heaven forfend, dynamically generated at run time running an application regression test, capturing the submitted SQL and proceeding as above may work.

We have had success with a .Net SQL parsing library. Its name eludes me just now. Should I find it I'll edit it into this answer. You'll still have to pull the SQL from your application, of course.

Related Solutions

MySQL – Script to Automate Creation of Audit Tables and Triggers

I just wrote this earlier today. It's a select statement working off the information_schema database, which produces the schema for the audit tables and the triggers.

SET GLOBAL group_concat_max_len = 1000;

SET @dbName = "[[[your_db_name_here]]]";

SELECT concat("DROP TABLE IF EXISTS `", @dbName, "`.`", table_data.audit_table, "`;\r",
          "CREATE TABLE `", @dbName, "`.`", table_data.audit_table, "`\r",
          "(\r",
          "  `auditAction` ENUM ('INSERT', 'UPDATE', 'DELETE'),\r",
          "  `auditTimestamp` DATETIME DEFAULT CURRENT_TIMESTAMP,\r",
          "  `auditId` INT(14) AUTO_INCREMENT,",
          column_defs, ",\r"
          "  PRIMARY KEY (`auditId`),\r",
          "  INDEX (`auditTimestamp`)\r",
          ")\r",
          "  ENGINE = InnoDB;\r\r",
          "DROP TRIGGER IF EXISTS `", @dbName, "`.`", table_data.insert_trigger, "`;\r",
          "CREATE TRIGGER `", @dbName, "`.`", table_data.insert_trigger, "`\r",
          "  AFTER INSERT ON `", @dbName, "`.`", table_data.db_table, "`\r",
          "  FOR EACH ROW INSERT INTO `", @dbName, "`.`", table_data.audit_table, "`\r",
          "     (`auditAction`,", table_data.column_names, ")\r",
          "  VALUES\r",
          "     ('INSERT',", table_data.NEWcolumn_names, ");\r\r",
          "DROP TRIGGER IF EXISTS `", @dbName, "`.`", table_data.update_trigger, "`;\r",
          "CREATE TRIGGER `", @dbName, "`.`", table_data.update_trigger, "`\r",
          "  AFTER UPDATE ON `", @dbName, "`.`", table_data.db_table, "`\r",
          "  FOR EACH ROW INSERT INTO `", @dbName, "`.`", table_data.audit_table, "`\r",
          "     (`auditAction`,", table_data.column_names, ")\r",
          "  VALUES\r",
          "     ('UPDATE',", table_data.NEWcolumn_names, ");\r\r",
          "DROP TRIGGER IF EXISTS `", @dbName, "`.`", table_data.delete_trigger, "`;\r",
          "CREATE TRIGGER `", @dbName, "`.`", table_data.delete_trigger, "`\r",
          "  AFTER DELETE ON `", @dbName, "`.`", table_data.db_table, "`\r",
          "  FOR EACH ROW INSERT INTO `", @dbName, "`.`", table_data.audit_table, "`\r",
          "     (`auditAction`,", table_data.column_names, ")\r",
          "  VALUES\r",
          "     ('DELETE',", table_data.OLDcolumn_names, ");\r\r"
)
FROM (
   # This select builds a derived table of table names with ordered and grouped column information in different
   # formats as needed for audit table definitions and trigger definitions.
   SELECT
     table_order_key,
     table_name                                                                      AS db_table,
     concat("audit_", table_name)                                                    AS audit_table,
     concat(table_name, "_inserts")                                                  AS insert_trigger,
     concat(table_name, "_updates")                                                  AS update_trigger,
     concat(table_name, "_deletes")                                                  AS delete_trigger,
     group_concat("\r  `", column_name, "` ", column_type ORDER BY column_order_key) AS column_defs,
     group_concat("`", column_name, "`" ORDER BY column_order_key)                   AS column_names,
     group_concat("`NEW.", column_name, "`" ORDER BY column_order_key)               AS NEWcolumn_names,
     group_concat("`OLD.", column_name, "`" ORDER BY column_order_key)               AS OLDcolumn_names
   FROM
     (
       # This select builds a derived table of table names, column names and column types for
       # non-audit tables of the specified db, along with ordering keys for later order by.
       # The ordering must be done outside this select, as tables (including derived tables)
       # are by definition unordered.
       # We're only ordering so that the generated audit schema maintains a resemblance to the
       # main schema.
       SELECT
         information_schema.tables.table_name        AS table_name,
         information_schema.columns.column_name      AS column_name,
         information_schema.columns.column_type      AS column_type,
         information_schema.tables.create_time       AS table_order_key,
         information_schema.columns.ordinal_position AS column_order_key
       FROM information_schema.tables
         JOIN information_schema.columns
           ON information_schema.tables.table_name = information_schema.columns.table_name
       WHERE information_schema.tables.table_schema = @dbName
             AND information_schema.columns.table_schema = @dbName
             AND information_schema.tables.table_name NOT LIKE "audit\_%"
     ) table_column_ordering_info
   GROUP BY table_name
 ) table_data
ORDER BY table_order_key
INTO OUTFILE "[[[your_output_file]]]"

T-SQL Optimization – Create a Pivot or Alter This Query?

This should work. I took the original CTE, partitioned by part and date and numbered those. This gave every part on each date it's own number starting with 1.

Then in the case statement, adding WHERE Number = N allows it so that each part is unique.

Finally, the column names were modified to have their respective numbers.

Since this is unwieldy, dynamic sql could be used to generate the repetitive code.

    With CTE AS (SELECT * FROM 
(SELECT ROW_NUMBER() OVER(PARTITION BY Part + Pull_Date ORDER BY PartSN) AS [Number], 
  Pull_Date, 
  Well_Name, 
  Part, 
  PartSN, 
  PartPN 
FROM (
SELECT Pull_Date, Well_Name, Part1 Part, Part1_PN PartPN, Part1_SN PartSN 
  FROM test
UNION ALL
    SELECT Pull_Date, Well_Name, Part2, Part2_PN, Part2_SN from test
UNION ALL
    SELECT Pull_Date, Well_Name, Part3, Part3_PN, Part3_SN from test
UNION ALL
    SELECT Pull_Date, Well_Name, Part4, Part4_PN, Part4_SN from test
UNION ALL
    SELECT Pull_Date, Well_Name, Part5, Part5_PN, Part5_SN from test
UNION ALL
    SELECT Pull_Date, Well_Name, Part6, Part6_PN, Part6_SN from test
) subquery) new
  )

 Select Pull_Date, Well_Name,
    MIN(CASE WHEN Part='BODH' AND Number=1 THEN 'BODH' ELSE NULL END) [BODH1],
    MIN(CASE WHEN Part='BODH' AND Number=1 THEN PartPN Else NULL END) BODH_PN1,
    MIN(CASE WHEN Part='BODH' AND Number=1 THEN PartSN ELSE NULL END) BODH_SN1,
    MIN(CASE WHEN Part='BODH' AND Number=2 THEN 'BODH' ELSE NULL END) [BODH2],
    MIN(CASE WHEN Part='BODH' AND Number=2 THEN PartPN Else NULL END) BODH_PN2,
    MIN(CASE WHEN Part='BODH' AND Number=2 THEN PartSN ELSE NULL END) BODH_SN2,

    MIN(CASE WHEN Part='Cable' AND Number=1 THEN 'Cable' ELSE NULL END) [Cable1],
    MIN(CASE WHEN Part= 'Cable' AND Number=1 THEN PartPN Else NULL END) Cable_PN1,
    MIN(CASE WHEN Part= 'Cable' AND Number=1 THEN PartSN ELSE NULL END) Cable_SN1,
    MIN(CASE WHEN Part='Cable' AND Number=2 THEN 'Cable' ELSE NULL END) [Cable2],
    MIN(CASE WHEN Part= 'Cable' AND Number=2 THEN PartPN Else NULL END) Cable_PN2,
    MIN(CASE WHEN Part= 'Cable' AND Number=2 THEN PartSN ELSE NULL END) Cable_SN2,

    MIN(CASE WHEN Part= 'Pump' AND Number=1 THEN 'Pump' ELSE Null END) [Pump1],
    MIN(CASE WHEN Part= 'Pump' AND Number=1 THEN PartPN ELSE NULL END) Pump_PN1,
    MIN(CASE WHEN Part= 'Pump' AND Number=1 THEN PartSN ELSE NULL END) Pump_SN1,
    MIN(CASE WHEN Part= 'Pump' AND Number=2 THEN 'Pump' ELSE Null END) [Pump2],
    MIN(CASE WHEN Part= 'Pump' AND Number=2 THEN PartPN ELSE NULL END) Pump_PN2,
    MIN(CASE WHEN Part= 'Pump' AND Number=2 THEN PartSN ELSE NULL END) Pump_SN2,

    MIN(CASE WHEN Part= 'MLE' AND Number=1 THEN 'MLE' ELSE NULL END) [MLE1],
    MIN(CASE WHEN Part= 'MLE' AND Number=1 THEN PartPN ELSE NULL END) MLE_PN1,
    MIN(CASE WHEN Part= 'MLE' AND Number=1 THEN PartSN ELSE NULL END) MLE_SN1,
    MIN(CASE WHEN Part= 'MLE' AND Number=2 THEN 'MLE' ELSE NULL END) [MLE2],
    MIN(CASE WHEN Part= 'MLE' AND Number=2 THEN PartPN ELSE NULL END) MLE_PN2,
    MIN(CASE WHEN Part= 'MLE' AND Number=2 THEN PartSN ELSE NULL END) MLE_SN2,

    MIN(CASE WHEN Part= 'AGH' AND Number=1 THEN 'AGH' ELSE NULL END) [AGH1],
    MIN(CASE WHEN Part= 'AGH' AND Number=1 THEN PartPN ELSE NULL END) AGH_PN1,
    MIN(CASE WHEN Part= 'AGH' AND Number=1 THEN PartSN ELSE NULL END) AGH_SN1,
    MIN(CASE WHEN Part= 'AGH' AND Number=2 THEN 'AGH' ELSE NULL END) [AGH2],
    MIN(CASE WHEN Part= 'AGH' AND Number=2 THEN PartPN ELSE NULL END) AGH_PN2,
    MIN(CASE WHEN Part= 'AGH' AND Number=2 THEN PartSN ELSE NULL END) AGH_SN2

FROM CTE
GROUP BY Pull_Date, Well_Name

Best Answer

Related Solutions

MySQL – Script to Automate Creation of Audit Tables and Triggers

T-SQL Optimization – Create a Pivot or Alter This Query?

Related Question