April 16, 2014 at 4:05 am
I have view somthing like
Create view All_employee
AS
SELECT Emp_Name, Emp_code FROM dbo.Employee
UNION ALL
SELECT Emp_Name, Emp_code FROM Emp_201402.Employee
But we have a different "Schema" for same table because we have archive table with same table name but with different schema name. Now we have req to make view which contain data of all table. But I can't seem to figure out how to do it in a view.
SET NOCOUNT ON
DECLARE @Count INT, @TotalCount INT, @SQL VARCHAR( MAX )
DECLARE @Schema TABLE ( ID INT, NAME VARCHAR(512) )
INSERT INTO @Schema
SELECT ROW_NUMBER() OVER (ORDER BY SCHEMA_ID), Name FROM sys.schemas where name like '%emloyee%' ORDER BY schema_id ASC
SELECT @TotalCount = COUNT( 1 ), @Count = 1 FROM @Schema
SELECT @SQL = 'SELECT * FROM dbo.emloyee'
WHILE (@TotalCount > @Count)
BEGIN
SELECT @SQL = @SQL + CHAR(13)
SELECT @SQL = @SQL + 'UNION ALL' + CHAR(13)
SELECT@SQL = @SQL + 'SELECT * FROM ' + Name + '.emloyee'
FROM@Schema
WHEREID = @Count
SELECT @Count = @Count + 1
END
PRINT @SQL
EXEC ( @SQL )
Don' think that works.
Is this possible with a view or it other way to do it?
Thanks,
Hitesh Sharma
🙂
Note: I am writing these first time in blog, let me know for more info.
April 16, 2014 at 5:06 am
This should get you started, cannot do this in a view though, in a stored procedure it works fine.
😎
USE [tempdb]
GO
CREATE SCHEMA [SCHEMA01] AUTHORIZATION [dbo];
GO
CREATE SCHEMA [SCHEMA02] AUTHORIZATION [dbo];
GO
CREATE SCHEMA [SCHEMA03] AUTHORIZATION [dbo];
GO
CREATE SCHEMA [SCHEMA04] AUTHORIZATION [dbo];
GO
CREATE TABLE SCHEMA01.TBL_MYSTUFF (MYSTUFF_ID INT NOT NULL);
GO
CREATE TABLE SCHEMA02.TBL_MYSTUFF (MYSTUFF_ID INT NOT NULL);
GO
CREATE TABLE SCHEMA03.TBL_MYSTUFF (MYSTUFF_ID INT NOT NULL);
GO
CREATE TABLE SCHEMA04.TBL_MYSTUFF (MYSTUFF_ID INT NOT NULL);
GO
SELECT * FROM INFORMATION_SCHEMA.TABLES T WHERE T.TABLE_NAME = N'TBL_MYSTUFF'
GO
INSERT INTO SCHEMA01.TBL_MYSTUFF(MYSTUFF_ID)
VALUES ( 1),( 2),( 3),( 4),( 5);
INSERT INTO SCHEMA02.TBL_MYSTUFF(MYSTUFF_ID)
VALUES ( 6),( 7),( 8),( 9),(10);
INSERT INTO SCHEMA03.TBL_MYSTUFF(MYSTUFF_ID)
VALUES (11),(12),(13),(14),(15);
INSERT INTO SCHEMA04.TBL_MYSTUFF(MYSTUFF_ID)
VALUES (16),(17),(18),(19),(20);
GO
DECLARE @SQL_STR NVARCHAR(MAX) = N'';
SELECT @SQL_STR = STUFF(
(
SELECT NCHAR(13) + NCHAR(10) + 'UNION ALL ' + N'SELECT * FROM ' + T.TABLE_SCHEMA + N'.TBL_MYSTUFF' AS [text()]
FROM INFORMATION_SCHEMA.TABLES T
WHERE T.TABLE_NAME = N'TBL_MYSTUFF'
FOR XML PATH(''), TYPE).value('.[1]','NVARCHAR(MAX)'),1,12,'')
EXEC sp_executesql @SQL_STR;
DROP TABLE SCHEMA01.TBL_MYSTUFF
DROP TABLE SCHEMA02.TBL_MYSTUFF
DROP TABLE SCHEMA03.TBL_MYSTUFF
DROP TABLE SCHEMA04.TBL_MYSTUFF
GO
DROP SCHEMA [SCHEMA01];
GO
DROP SCHEMA [SCHEMA02];
GO
DROP SCHEMA [SCHEMA03];
GO
DROP SCHEMA [SCHEMA04];
GO
April 16, 2014 at 5:11 am
the problem there is security, though, right?
an end user needs access to each of those tables in each of the schemas in order to use the view; ownership chaining gets disabled.
you could use a stored procedure signed with a certificate, if you can call a proc instead of a view.
Lowell
April 16, 2014 at 12:46 pm
Hi Eirikur ,
Thank's for reply , Actually we want to use as a table which contain all data in a single view in which we also apply "WHERE" clause if necessary.
is there is a way to do like that.?
April 16, 2014 at 1:41 pm
Hi Lowell,
security is not a major issue but we want to merge all table in view in which we also able to use WHERE clause, but currently we use like that:-
CREATE VIEW dbo.All_employee
AS
BEGIN
SELECT * FROM dbo.Employee
UNION ALL
SELECT * FROM Sch_201401.Employee
UNION ALL
SELECT * FROM Sch_201402.Employee
UNION ALL
SELECT * FROM Sch_201403.Employee
UNION ALL
SELECT * FROM Sch_201404.Employee
END
but table is increased day by day we have to add manually on VIEW , So i want to create dynamic VIEW which will ADD Archive table dynamically.
April 16, 2014 at 4:00 pm
You can't have dynamic code in the view. But you can write dynamic script (or procedure) that will generate DROP and CREATE VIEW statements (or ALTER VEW) with all your current tables and then execute it. You can then schedule this script as a job an run it every day after new table is created.
After a year it's going to look kinda ugly. I wonder if there is a limit on a number of UNIONs in a query?
Or are you going to drop those archive tables periodically? Why not simply have one history table with a date stamp and keep adding to it?
--Vadim R.
April 16, 2014 at 11:48 pm
I agree with rVadim that you should probably consider another approach if possible.
Having said this, here an example code for a procedure to drop and create the view:
CREATE PROCEDURE dbo.USP_REFRES_THE_LATEST_VIEW
AS
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'THE_LATEST_VIEW'
AND TABLE_SCHEMA = N'dbo' AND TABLE_TYPE = N'VIEW') DROP VIEW dbo.THE_LATEST_VIEW;
DECLARE @SQL_STR NVARCHAR(MAX) = N'';
SELECT @SQL_STR = STUFF(
(
SELECT NCHAR(13) + NCHAR(10) + 'UNION ALL ' + N'SELECT * FROM ' + T.TABLE_SCHEMA + N'.TBL_MYSTUFF' AS [text()]
FROM INFORMATION_SCHEMA.TABLES T
WHERE T.TABLE_NAME = N'TBL_MYSTUFF'
FOR XML PATH(''), TYPE).value('.[1]','NVARCHAR(MAX)'),1,12,'');
SELECT @SQL_STR = N'CREATE VIEW dbo.THE_LATEST_VIEW AS ' + @SQL_STR;
EXEC sp_executesql @SQL_STR;
April 17, 2014 at 2:11 am
Hi rVadim,
Actually if we have one table it create other problem for performance as the table grows , so we are thinking by using in view.
Thank's for your suggestion of creating proc. which create dynamics view.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply