Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Unanswered: create views on different schema

    Hi all I created a different schema for the users so they can create views and sps without messing up anything. but I am curious as to how do I alter the views to the new schema..instead of dbo. would be users. Also do I need to change the tables to the users schmea also??



    Code:
    ALTER VIEW dbo.[People_TanfActivity_view]
    AS
    SELECT     dbo.People_tbl.[Parent ID], dbo.People_tbl.[Family ID], dbo.People_tbl.StudentID, dbo.People_tbl.[Referral Location], dbo.People_tbl.[Referral Date], 
                          dbo.People_tbl.[Registration Date], dbo.People_tbl.[Student First Name], dbo.People_tbl.[Student Last Name], dbo.People_tbl.[Student SS#], 
                          dbo.People_tbl.[Student Sex], dbo.People_tbl.Grade, dbo.People_tbl.Age, dbo.People_tbl.[Student Date of Birth], dbo.People_tbl.[Event ID], 
                          dbo.People_tbl.StudentZip, dbo.People_tbl.StudentState, dbo.People_tbl.StudentAddress, dbo.People_tbl.StudentCity, dbo.People_tbl.Phone, 
                          dbo.People_tbl.Message, dbo.People_tbl.Adult_Child, dbo.People_tbl.State, dbo.People_tbl.SSID, dbo.People_tbl.TANF, dbo.People_tbl.Weekly, 
                          dbo.People_tbl.Month, dbo.People_tbl.ReferralStatus, dbo.TanfActivity_tbl.EventDate, dbo.TanfActivity_tbl.[Type of Contact], 
                          dbo.TanfActivity_tbl.[Referral Date] AS Expr1, dbo.TanfActivity_tbl.[Purpose of Contact], dbo.TanfActivity_tbl.[Earned hours], 
                          dbo.TanfActivity_tbl.[Catagory for hours], dbo.TanfActivity_tbl.[Services Covered], dbo.TanfActivity_tbl.[State Catagory], 
                          dbo.TanfActivity_tbl.[State Services Covered], dbo.TanfActivity_tbl.Month AS Expr2, dbo.TanfActivity_tbl.Week, 
                          dbo.TanfActivity_tbl.ScairCaseWorker
    FROM         dbo.People_tbl INNER JOIN
                          dbo.TanfActivity_tbl ON dbo.People_tbl.[Parent ID] = dbo.TanfActivity_tbl.[Parent ID]

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Code:
     
    alter schema <new_schema> transfer dbo.People_TanfActivity_view
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •