dealing with sys tables as any other table

i don't have a good experience with sys tables generated by MS SQL Such as INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS

can i copy these views found under DBName\System Views\ to another databas开发者_开发知识库e and deal with them like any other tables (deal with them i mean issue sql queries)?

are there any considerations that i should care of?

edit

the reason i need to do this is because SQL Azure does not support sys tables. I am using the sys tables in my application, therefore if i copy them to SQL Azure as normal tables, would everything work fine?

SQL Azure doesn't support information_schema but it does support the sys.* tables (at least the ones you need).

http://msdn.microsoft.com/en-us/library/ee336238.aspx#catalog

You can use the following:

Select * from sys.foreign_keys fk
inner join sys.foreign_key_columns fkc on fkc.constraint_object_id = fk.object_id

In order for you to get the columns involved or index information look into the following sys tables sys.tables, sys.indexes, and sys.columns. The standard sys catalog views are a little bit harder to use than the views on top of them that comply with ANSI standards.

The database trigger I talked about in my comment is below. This does not handle the instance of the creation of an fk on table create but could easily be expanded to handle other situations. This will ensure that this information is always accurate implemented as a ddl trigger

--drop trigger test on database
create trigger test on DATABASE
for ALTER_TABLE,RENAME
AS
    set nocount on

    --get XML event and extract table and schema
    DECLARE @data XML
    SET @data = EVENTDATA()

    declare @idoc int
    EXEC sp_xml_preparedocument @idoc OUTPUT, @data

    --indicate if table needs to be reloaded
    declare @boolean bit
    set @boolean = 0

    --check conditions where keys change
    SELECT  @boolean = 1
    FROM       OPENXML (@idoc, '/EVENT_INSTANCE/AlterTableActionList/Create/Constraints',2) x
    SELECT  @boolean = 1
    FROM       OPENXML (@idoc, '/EVENT_INSTANCE/AlterTableActionList/Drop/Constraints',2) x

    if @boolean =1
    begin
        --Create the table initially with below line
        --select * into MyInformationSchemaFKs from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
        --Reloads the table if there are constraint changes
        truncate table MyInformationSchemaFKs
        insert into MyInformationSchemaFKs
        select *  from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS 
    end

    EXEC sp_xml_removedocument @idoc


go