My stored procedure takes 6 parameters and given those parameters it looks through all the tables in a database and tells you which tables have the specified data.
For example:
sp_cis_key_combo_snapshot_ListTables '123','',NULL,NULL,'%',NULL
This would return a list of all the tables that have ALL of that data, aka any table that has Incident_Nr = '123', Agency = '', and Module_ID is anything but empty.
The parameter NULL will mean that the column can or cannot exist in that table, aka it doesn't matter.
The parameter '' will mean that the column has to exist, but has to be empty.
The parameter '%' will mean that the column has to exist, but cannot be empty.
If the parameter is anything but what is listed above like 'ABC' then it must return a column with that exactly.
My stored procedure has no use for parameters like 'A%C', but it can take them.
Now the reason I need this is that "my" database is terribly unorganized and I need to know how certain tables relate to each other, without going into detail, how would I speed up this stored procedure?
USE [RMS]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_cis_key_combo_snapshot_ListTables] (
@incident_nr VARCHAR(12),
@agency VARCHAR(4),
@suffix1 VARCHAR(3),
@suffix2 VARCHAR(3),
@module_id VARCHAR(3),
@type VARCHAR(2))
AS
BEGIN
SET NOCOUNT ON
DECLARE @tables TABLE (TABLE_NAME SYSNAME)
DECLARE @columns TABLE (COLUMN_NAME SYSNAME)
INSERT INTO @tables
SELECT [TABLE_NAME] = NAME
FROM sysobjects
WHERE type = 'U'
ORDER BY NAME
DECLARE @table_name SYSNAME
DECLARE @has_incident_nr_flag INT
DECLARE @has_agency_flag INT
DECLARE @has_suffix1_flag INT
DECLARE @has_suffix2_flag INT
DECLARE @has_module_id_flag INT
DECLARE @has_type_flag INT
DECLARE @total_flag INT
DECLARE @incident_nr_query VARCHAR(1000)
DECLARE @agency_query VARCHAR(1000)
DECLARE @suffix1_query VARCHAR(1000)
DECLARE @suffix2_query VARCHAR(1000)
DECLARE @module_id_query VARCHAR(1000)
DECLARE @type_query VARCHAR(1000)
start_loop:
SET @table_name = ''
SELECT TOP 1 @table_name = TABLE_NAME
FROM @tables
IF @table_name = ''
GOTO exit_loop
DELETE
FROM @tables
WHERE TABLE_NAME = @table_name
INSERT INTO @columns
SELECT [COLUMN_NAME] = c.NAME
FROM sysobjects t
INNER JOIN syscolumns c ON c.id = t.id
WHERE t.NAME = @table_name
ORDER BY c.colid
SELECT @has_incident_nr_flag = 0, @has_agency_flag = 0, @has_suffix1_flag = 0, @has_suffix2_flag = 0, @has_module_id_flag = 0, @has_type_flag = 0, @total_flag = 0
IF (@incident_nr IS NOT NULL)
SET @total_flag = @total_flag + 32
IF (@agency IS NOT NULL)
SET @total_flag = @total_flag + 16
IF (@suffix1 IS NOT NULL)
SET @total_flag = @total_flag + 8
IF (@suffix2 IS NOT NULL)
SET @total_flag = @total_flag + 4
IF (@module_id IS NOT NULL)
SET @total_flag = @total_flag + 2
IF (@type IS NOT NULL)
SET @total_flag = @total_flag + 1
IF EXISTS (
SELECT *
FROM @columns
WHERE column_name = 'Incident_Nr'
)
AND @incident_nr IS NOT NULL
SET @has_incident_nr_flag = 32
IF EXISTS (
SELECT *
FROM @columns
WHERE column_name = 'Agency'
)
AND @agency IS NOT NULL
SET @has_agency_flag = 16
IF EXISTS (
SELECT *
FROM @columns
WHERE column_name = 'Suffix_1'
)
AND @suffix1 IS NOT NULL
SET @has_suffix1_flag = 8
IF EXISTS (
SELECT *
FROM @columns
WHERE column_name = 'Suffix_2'
)
AND @suffix2 IS NOT NULL
SET @has_suffix2_flag = 4
IF EXISTS (
SELECT *
FROM @columns
WHERE column_name = 'Module_ID'
)
AND @module_id IS NOT NULL
SET @has_module_id_flag = 2
IF EXISTS (
SELECT *
FROM @columns
WHERE column_name = 'Type'
)
AND @type IS NOT NULL
SET @has_type_flag = 1
SET @incident_nr_query = ' '
SET @agency_query = ' '
SET @suffix1_query = ' '
SET @suffix2_query = ' '
SET @module_id_query = ' '
SET @type_query = ' '
IF (@has_incident_nr_flag = 32)
SET @incident_nr_query = 'AND Incident_Nr LIKE ' + '''' + @incident_nr + '''' + ' '
IF (@has_agency_flag = 16)
SET @agency_query = 'AND Agency LIKE ' + '''' + @agency + '''' + ' '
IF (@has_suffix1_flag = 8)
SET @suffix1_query = 'AND Suffix_1 LIKE ' + '''' + @suffix1 + '''' + ' '
IF (@has_suffix2_flag = 4)
SET @suffix2_query = 'AND Suffix_2 LIKE ' + '''' + @suffix2 + '''' + ' '
IF (@has_module_id_flag = 2)
SET @module_id_query = 'AND Module_ID LIKE ' + '''' + @module_id + '''' + ' '
IF (@has_type_flag = 1)
SET @type_query = 'AND Type LIKE ' + '''' + @type + '''' + ' '
IF (@total_flag = @has_incident_nr_flag + @has_agency_flag + @has_suffix1_flag + @has_suffix2_flag + @has_module_id_flag + @has_type_flag)
BEGIN
DECLARE @sql VARCHAR(1000)
SET @sql = 'IF EXISTS (
SELECT *
FROM dbo.' + @table_name + '
WHERE 1=1 ' + @incident_nr_query + @agency_query + @suffix1_query + @suffix2_query + @module_id_query + @type_query + '
) ' + 'BEGIN print ' + '''' + @table_name + '''' + ' END'
EXEC (@sql)
END
DELETE @columns
GOTO start_loop
exit_loop:
END
