November 25, 2009 at 11:49 am
I'm a beginner DBA trying to working with a very old application that is using SS2005. Trying to use Reporting services to create automated reports for a database that is poorly designed.
The database has tables such as
group_1
group_2
group_3
.
.
group_25
etc..
Where the application can create and delete tables periodically. I am tasked with managing several databases with varying structures and different numbers of group_? tables. What I have done to resolve this problem is to create a view that unions together all the group_? tables and adds an index column.
Create View [dbo].[Groups]
Select '1' AS [Group_Key], * FROM group_1
UNION ALL
Select '2' AS [Group_Key], * FROM group_2
UNION ALL
Select '3' AS [Group_Key], * FROM group_3
I can build my reports from this Groups view, however I am creating and altering the view each time a group table is added or removed.
What I would like is a way of building union view based on the groups that exsist in each database. I have been able to extract the tables using the following:
SELECT name FROM sys.sysobjects
WHERE (name LIKE N'group___') or (name LIKE N'group__')
But I'm not sure how to build the union using this table list with the included index number.
Appriciate any help,
November 25, 2009 at 12:30 pm
are the group tables all the same structure? If not, then are you stubbing out the fields that don't exist between tables? do all the group tables start with group? Can you supply a script of maybe 3 or 4 tables and layouts?
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 25, 2009 at 7:21 pm
Thanks for the reply. This is my first post, so please forgive the sloppy formating of my post.
Yes, all tables are of the exact same structure.
Yes they are all named group_# and incremented sequentially
group_1
group_2
group_3
.
.
group_10
group_11
etc..
I want to combine all fields from all existing group tables into one table/view and add an index number to indicate which group table was the source.
November 26, 2009 at 1:27 pm
This should do it...
--===== Code to create the view syntax in a variable
DECLARE @SQL1 VARCHAR(MAX)
SELECT @SQL1 = 'CREATE VIEW dbo.Groups AS' + CHAR(10)
+ STUFF( -- Gets rid of first UNION ALL
( --=== Finds the correct table names and concatenates the
-- the necessary syntax for each table name to build
-- view using a "blank" untagged XML Path
SELECT 'UNION ALL SELECT '
+ SUBSTRING(Name,CHARINDEX('_',Name)+1,256)
+ ' AS Group_Key, * FROM '
+ Name
+ CHAR(10)
FROM sys.SysObjects
WHERE Name LIKE 'Group__%' ESCAPE '_'
FOR XML PATH('')
)
,1,10,'')
--===== Conditionally drop the view
IF OBJECT_ID('dbo.Groups','V') IS NOT NULL
DROP VIEW dbo.Groups
--===== Create the view using the generated syntax
EXEC (@SQL1)
--===== Not required, but show the syntax we just executed
PRINT @SQL1
--Jeff Moden
Change is inevitable... Change for the better is not.
November 26, 2009 at 5:54 pm
I like Jeff's solution, but just wanted to offer an alternative that requires no user input beyond the initial running of the script....(I like not having to think)
Firstly, here is a database trigger that will alter the view whenever a table is added or dropped
(sorry the code formatter on here is displaying this wrong - but I have checked the code still works...)
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* Create a database trigger that will create/alter the view Groups whenever a group_?? table is created or dropped */
CREATE TRIGGER dynamic_view_trigger ON DATABASE
FOR CREATE_TABLE, DROP_TABLE
AS
SET ANSI_PADDING ON
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_WARNINGS ON
/* Check the table name matches the template we are interested in */
if EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','varchar(100)') LIKE 'group\_%' ESCAPE '\'
exec('
BEGIN TRY
DECLARE @sql VARCHAR(MAX)
SET @sql = ''''
/*
*
* Build up the dynamic SQL for the UNION selects
* The CASE statement ensures we get UNION in the right place
* The REPLACE strips out the table number from the name - could use SUBSTRING instead - for the GroupKey column
*
*/
CASE @sql
WHEN '''' THEN ''''
ELSE ''UNION ''
END +
''SELECT ''''''+REPLACE([name],''group_'','''')+'''''' [Group_Key], * FROM ''+[name]+CHAR(13)
FROM sysobjects WITH(NOLOCK)
WHERE [name] LIKE ''group\_%'' ESCAPE ''\''
/* Check if the View already exists - and prepend ALTER/CREATE as appropriate */
IF OBJECT_ID(''[dbo].[Groups]'') IS NULL
SET @sql = ''CREATE VIEW [dbo].[Groups] AS ''+@sql
ELSE
SET @sql = ''ALTER VIEW [dbo].[Groups] AS ''+@sql
/* Create the view */
EXEC(@sql)
END TRY
BEGIN CATCH
/* I just do not want any trigger malfunction to affect the database users */
END CATCH
')
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER sample_db_trigger ON DATABASE
Next some sample sql with which to test it:
/* No need to leave a mess behind eh? */
BEGIN TRAN
/* Create some tables and drop a few rows in */
CREATE TABLE group_1(col1 INT);
INSERT group_1 VALUES(1);
CREATE TABLE group_2(col1 INT);
INSERT group_2 VALUES(2);
CREATE TABLE group_3(col1 INT);
INSERT group_3 VALUES(3);
INSERT group_3 VALUES(4);
CREATE TABLE group_4(col1 INT);
INSERT group_4 VALUES(5);
CREATE TABLE group_5(col1 INT);
INSERT group_5 VALUES(6);
/* Test the view works */
EXEC('SELECT * FROM Groups');
/* Drop a table to see if the view copes */
DROP TABLE group_3;
EXEC('SELECT * FROM Groups');
/* clean up */
ROLLBACK
Kind of nice, but I expect there could be issues with performance if there were LOTS of group_?? tables. 😀
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);November 27, 2009 at 12:03 am
I can't help myself, I have to ask, what kind of application is this and why does it create many tables with the exact same schema??
November 29, 2009 at 6:52 pm
Thks mangoo, the trigger is exactly what I was looking for. You guys are the best. This is for a very specialized old application and is not very well designed from a database standpoint, but the company doesn't want to invest in developing a new one at this time. I'll just have to keep trying to deal with it for the time being.
Thanks again for your help.
November 29, 2009 at 7:11 pm
mister.magoo (11/26/2009)
...wanted to offer an alternative that requires no user input beyond the initial running of the script....(I like not having to think)
How did the ol' Magoo cartoons always end? "Ah, Mister Magoo... you've done it again."
Nicely done...
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply