Post reply

programatically detemine VIEW source

  • March 5, 2007 at 10:37 am

    #177049

    Currently I have a nightly process that does a datapull from a 3rd party Sql Server database into our datawarehouse. The users need the data in the warehouse to be updated more then nightly.

    The plan is to create two tables (userInformation1, userInformation2 )and two views ( vUserInformation, vUserInformationLoad). This is to allow the database to pull the data into vUserInformatinoLoad and then at the end of the pull have the last step execute some ddl scripts on the views.

    drop view vUserInformation

    drop view vUserInformationLoad

    create view vUserInformation as select * from userInformation1

    create view vUserInformationLoad as select * from userInformation2

    This would allow the current data to be available to the user and then when a load is ran at the end the warehouse has the latest greatest with very little down time.

    QUESTION: How to programmatically determine the source for the view? (which table is the view currently point at?)

    Note: searching for 'view' information is so fun. Everything view this view that.

    thanks

    Daryl

  • March 5, 2007 at 12:05 pm

    #693189

    I might be mis-understanding the question, but...

    if you are re-using the same view name, and recreating it with different SELECT statements, then you should be able to use the command EXEC sp_depends VIEWNAME;

    that will list all tables and columns referenced in the view: here's some sample output:

    nametypeupdatedselectedcolumn
    dbo.TBSUBOUTuser tablenonoDESCRIP
    dbo.TBGMSTATuser tablenonoSTATUSTBLKEY
    dbo.TBGMSTATuser tablenonoDESCRIP
    dbo.GMOUTCOMuser tablenonoSUBOUTTBLKEY
    dbo.GMOUTRSLuser tablenonoRESULTTBLKEY
    dbo.TBSUBOUTuser tablenonoSUBOUTTBLKEY
    dbo.GMACTOUTuser tablenonoRESULTTBLKEY
    dbo.GMOUTCOMuser tablenonoACTTYOUTTBLKEY
    dbo.GMOUTCOMuser tablenonoOUTCOMETBLKEY
    dbo.GMACTOUTuser tablenonoACTUAL
    dbo.GMACTOUTuser tablenonoSTATUSTBLKEY
    dbo.GMACTOUTuser tablenonoOUTCOMEDT
    dbo.GMACTOUTuser tablenonoACTTBLKEY
    dbo.GMACTOUTuser tablenonoACTTYOUTTBLKEY
    dbo.GMACTOUTuser tablenonoPROPOSED
    dbo.GMACTuser tablenonoACTNBR
    dbo.GMACTuser tablenonoACTNAME
    dbo.GMACTuser tablenonoIDISACTNBR
    dbo.TBOUTCOMuser tablenonoOUTCOMETBLKEY
    dbo.TBOUTCOMuser tablenonoDESCRIP
    dbo.GMACTuser tablenonoACTTBLKEY

    if you want the View's definition, then exec sp_helptext VIEWNAME would give you the raw source?

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • March 5, 2007 at 9:23 pm

    #693238

    Either the sp_helptext or sp_depends will do grand!

    I will be creating a temp (or variable table) and checking the results to determine which way to switch the data and load views.

    thanks

    Daryl

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply