November 23, 2005 at 4:20 pm
Hi,
Using SQL 2000 Standard.
Have a partitioned view with 7 tables. Each table has a date range which represents a 'sliding' week.
How can I read back the value of a check constraint so that I can easily find the table to update - as the view is not updatable (unless Enterprise ??).
Thanks
Regards
Graham
November 23, 2005 at 7:54 pm
The view would be updatable if the partitioning column is part of the primary key and the SQL Server Edition is Enterprise or Developer.
To get the underlying tables and the check constraint source, include is SQL using the ANSI standard INFORMATION_SCHEMA and also directly accessing the system tables. Using the system tables runs faster.
Try the below SQL.
The view would be updatable if the partitioning column is part of the primary key and the SQL Server Edition is Enterprise or Developer.
To get the underlying tables and the check constraint,
include is SQL using the ANSI standard INFORMATION_SCHEMA and also directly accessing the system tables.
Try the below SQL.
create table Foo_Week85
(FooIdint not null
,WeekNumber int not null
,OtherColumns int not null
, constraint Foo_Week85_P primary key (FooId, WeekNumber)
, constraint Foo_Week85_C_WeekNumber check (WeekNumber = 85 )
)
create table Foo_Week86
(FooIdint not null
,WeekNumber int not null
,OtherColumns int not null
, constraint Foo_Week86_P primary key (FooId, WeekNumber)
, constraint Foo_Week86_C_WeekNumber check (WeekNumber = 86 )
)
go
create view Foo
(FooId, WeekNumber , OtherColumns)
as
select FooId, WeekNumber , OtherColumns
from dbo.Foo_Week85
union all
select FooId, WeekNumber , OtherColumns
from dbo.Foo_Week86
go
insert into Foo
(FooId, WeekNumber , OtherColumns)
select 2 , 85, 8 union all
select 2 , 86, 6
go
select 'Foo_Week85' , * from Foo_Week85
select 'Foo_Week86', * from Foo_Week86
go
select VIEW_TABLE_USAGE.TABLE_SCHEMA
,VIEW_TABLE_USAGE.TABLE_NAME
,CHECK_CONSTRAINTS.CHECK_CLAUSE
,Substring(CHECK_CONSTRAINTS.CHECK_CLAUSE
, charindex( '= ' , CHECK_CONSTRAINTS.CHECK_CLAUSE) + 2
, charindex( ')' , CHECK_CONSTRAINTS.CHECK_CLAUSE)
- charindex( '= ' , CHECK_CONSTRAINTS.CHECK_CLAUSE) - 2
)
from INFORMATION_SCHEMA.VIEW_TABLE_USAGE as VIEW_TABLE_USAGE
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS as TABLE_CONSTRAINTS
on TABLE_CONSTRAINTS.TABLE_SCHEMA= VIEW_TABLE_USAGE.TABLE_SCHEMA
and TABLE_CONSTRAINTS.TABLE_NAME= VIEW_TABLE_USAGE.TABLE_NAME
join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE as CONSTRAINT_COLUMN_USAGE
on CONSTRAINT_COLUMN_USAGE.TABLE_SCHEMA = TABLE_CONSTRAINTS.TABLE_SCHEMA
and CONSTRAINT_COLUMN_USAGE.TABLE_NAME = TABLE_CONSTRAINTS.TABLE_NAME
JOIN INFORMATION_SCHEMA.CHECK_CONSTRAINTS as CHECK_CONSTRAINTS
on CHECK_CONSTRAINTS.CONSTRAINT_SCHEMA = CONSTRAINT_COLUMN_USAGE.CONSTRAINT_SCHEMA
and CHECK_CONSTRAINTS.CONSTRAINT_NAME = CONSTRAINT_COLUMN_USAGE.CONSTRAINT_NAME
WHERE VIEW_TABLE_USAGE.VIEW_SCHEMA = 'DBO'
and VIEW_TABLE_USAGE.VIEW_NAME = 'Foo'
and TABLE_CONSTRAINTS.CONSTRAINT_TYPE = 'CHECK'
and CONSTRAINT_COLUMN_USAGE.COLUMN_NAME = 'WeekNumber'
go
alter view TablePartions
(View_Schema, VIEW_NAME , TABLE_SCHEMA, TABLE_NAME, Column_Name
, PartitioningValue)
as
select ViewOwner.nameas View_Schema
, Views.name
,TableOwner.nameas TABLE_SCHEMA
,Tables.nameas TABLE_NAME
,syscolumns.name
,Substring(syscomments.text
, charindex( '= ' , syscomments.text) + 2
, charindex( ')' , syscomments.text)
- charindex( '= ' , syscomments.text) - 2
) as PartioningValue
from sysusersas ViewOwner
joinsysobjectsViews
on Views.Uid = ViewOwner.Uid
-- Dependent tables and columns
joinsysdependsViewTables
on ViewTables.id = Views.id
joinsysobjectsTables
on Tables.id = ViewTables.depid
joinsysusersas TableOwner
on TableOwner.Uid = Tables.Uid
joinsyscolumns
on syscolumns.id = Tables.id
and syscolumns.colid = ViewTables.depnumber
join sysobjectsas ColConstraints
onColConstraints.parent_obj = ViewTables.depid
and ColConstraints.info= ViewTables.depnumber
and ColConstraints.xtype= 'C'
join syscomments
onsyscomments.id = ColConstraints.id
go
select *
from TablePartions
where View_Schema= 'dbo'
and VIEW_NAME = 'foo'
and Column_Name = 'WeekNumber'
go
drop view Foo
go
drop table Foo_Week85
go
drop table Foo_Week86
go
SQL = Scarcely Qualifies as a Language
November 24, 2005 at 12:24 pm
Wow !!!
Thanks for this - not had time to try any of it out yet though.
The solution I came up with (by a DBA telling me to look at syscomments) was :
select o.name,
parsename(replace(replace(replace(replace(c.text, char(39) , '.'), '[', ''), ']', ''), '.)', ''), 3) as 'Start',
parsename(replace(replace(replace(replace(c.text, char(39) , '.'), '[', ''), ']', ''), '.)', ''), 1) as 'End'
from sysobjects o
INNER JOIN syscomments c ON o.Id = c.Id
where o.type = 'C'
Provided me with the constraint name, plus my start and end dates (parsed from the syscomments.text column).
Regards
Graham
November 24, 2005 at 6:41 pm
A typical practice with partioning is to have a job that runs on a scheduled basis that:
1. Creates a new table for the next time periods's data
2. Alters the view to include this new table and to remove the oldest table.
3. Drop the table containing the oldest time period.
Based on this practice, the only information known is the name of the view and the name of the partitioning column but the names of the underlying tables, the names of the check constraints and the source of the check constraint all need to determined from the information in the dictionary. Hence the manner in which the SQL was written.
SQL = Scarcely Qualifies as a Language
November 25, 2005 at 12:22 pm
Again - thanks for the assistance, I will be re-using the same tables simply be truncating the data and then dropping and recreating the constraint (I hope), hence the simpler approach.
Thanks
Regards
Graham
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply