Results 1 to 11 of 11

Thread: Basic Question

  1. #1
    Join Date
    Aug 2003
    Posts
    328

    Unanswered: Basic Question

    I have many views and views on these views. Is there anyone out there who might have a suggestion as to how I can keep them straight so that I won't lose track of what I made them for? I am starting to not remember why I made some, so I have to go back and try and remember. Any suggestion would be appreciated.

  2. #2
    Join Date
    Nov 2003
    Location
    Edinburgh
    Posts
    149

    Talking Re: Basic Question

    Make a table of your views.

  3. #3
    Join Date
    Aug 2003
    Posts
    328

    Re: Basic Question

    I thought about that and think that it is the best way. Thanks.

  4. #4
    Join Date
    Aug 2003
    Posts
    328
    Thanks.

  5. #5
    Join Date
    Feb 2002
    Posts
    2,232
    A long time ago we had the same issue with pointers. The best way to handle this is to create an object map/diagram which shows the views and their dependencies.

  6. #6
    Join Date
    Aug 2003
    Posts
    328
    Like an Excel Sheet?
    Thanks.

  7. #7
    Join Date
    Feb 2002
    Posts
    2,232
    Any type of modeling software like visio, rational, erwin ...

  8. #8
    Join Date
    Aug 2003
    Posts
    328
    Thats a good idea. Thanks.

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Or you can go NUTS....

    Just mine the parent child relationships with code...

    Code:
    -- In M$ infinite wisdom...the give us more than we need for this...the second result set 
    -- blows up the INSERT INTO...so lets build our own
    
    USE Northwind
    GO
    
    create procedure sp_depends2  --- 1996/08/09 16:51
    @objname nvarchar(776)		/* the object we want to check */
    as
    
    declare @objid int			/* the id of the object we want */
    declare @found_some bit			/* flag for dependencies found */
    declare @dbname sysname
    
    /*
    **  Make sure the @objname is local to the current database.
    */
    
    select @dbname = parsename(@objname,3)
    
    if @dbname is not null and @dbname <> db_name()
    	begin
    		raiserror(15250,-1,-1)
    		return (1)
    	end
    
    /*
    **  See if @objname exists.
    */
    select @objid = object_id(@objname)
    if @objid is null
    	begin
    		select @dbname = db_name()
    		raiserror(15009,-1,-1,@objname,@dbname)
    		return (1)
    	end
    
    /*
    **  Initialize @found_some to indicate that we haven't seen any dependencies.
    */
    select @found_some = 0
    
    set nocount on
    
    /*
    **  Print out the particulars about the local dependencies.
    */
    if exists (select *
    		from sysdepends
    			where id = @objid)
    begin
    	raiserror(15459,-1,-1)
    	select		 'name' = (s6.name+ '.' + o1.name),
    			 type = substring(v2.name, 5, 16),
    			 updated = substring(u4.name, 1, 7),
    			 selected = substring(w5.name, 1, 8),
                 'column' = col_name(d3.depid, d3.depnumber)
    		from	 sysobjects		o1
    			,master.dbo.spt_values	v2
    			,sysdepends		d3
    			,master.dbo.spt_values	u4
    			,master.dbo.spt_values	w5 --11667
    			,sysusers		s6
    		where	 o1.id = d3.depid
    		and	 o1.xtype = substring(v2.name,1,2) collate database_default and v2.type = 'O9T'
    		and	 u4.type = 'B' and u4.number = d3.resultobj
    		and	 w5.type = 'B' and w5.number = d3.readobj|d3.selall
    		and	 d3.id = @objid
    		and	 o1.uid = s6.uid
    		and deptype < 2
    
    	select @found_some = 1
    end
    
    /* Let's get rid of this part
    **  Now check for things that depend on the object.
    if exists (select *
    		from sysdepends
    			where depid = @objid)
    begin
    		raiserror(15460,-1,-1)
    	select distinct 'name' = (s.name + '.' + o.name),
    		type = substring(v.name, 5, 16)
    			from sysobjects o, master.dbo.spt_values v, sysdepends d,
    				sysusers s
    			where o.id = d.id
    				and o.xtype = substring(v.name,1,2) collate database_default and v.type = 'O9T'
    				and d.depid = @objid
    				and o.uid = s.uid
    				and deptype < 2
    
    	select @found_some = 1
    end
    
    */
    
    
    /*
    **  Did we find anything in sysdepends?
    */
    if @found_some = 0
    	raiserror(15461,-1,-1)
    
    set nocount off
    
    return (0) -- sp_depends
    GO
    
    
    -- Now on to our code
    
    CREATE VIEW myView01 AS SELECT * FROM Orders
    GO
    CREATE VIEW myView02 AS SELECT * FROM myView01
    GO
    CREATE VIEW myView03 AS SELECT * FROM myView02
    GO
    
    CREATE TABLE ViewDepends (
    	  ViewName   sysname NULL
    	, ViewDep    sysname NULL
    	, ViewType   varchar(50) NULL
    	, updated    varchar(5) NULL
    	, selected   varchar(5) NULL
    	, columnname sysname NULL)
    GO
    SET NOCOUNT ON
    DECLARE   @ViewName   sysname
    	, @SQL varchar(8000)
    
    DECLARE myDep CURSOR 
    FOR 
    SELECT 'INSERT INTO ViewDepends(ViewDep, ViewType, updated, selected, columnname) ' 	 
    	+ ' EXEC sp_depends2 [' + TABLE_NAME + ']' AS SQL
    	, TABLE_NAME 
      FROM INFORMATION_SCHEMA.Tables
     WHERE TABLE_TYPE = 'VIEW'
    
    OPEN myDep
    
    FETCH NEXT FROM myDep INTO @SQL, @ViewName
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
    --	SELECT @SQL
    	EXEC(@SQL)
    	UPDATE ViewDepends SET ViewName = @ViewName
    	 WHERE ViewName IS NULL
    	FETCH NEXT FROM myDep INTO @SQL, @ViewName
    END
    
    CLOSE myDep
    DEALLOCATE myDep
    SET NOCOUNT OFF
    GO
    
    SELECT * FROM ViewDepends ORDER BY ViewName
    
    GO
    
    DROP PROC sp_depends2
    DROP TABLE ViewDepends
    DROP VIEW myView01
    DROP VIEW myView02
    DROP VIEW myView03
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Dec 2002
    Posts
    1,245

    Re: Basic Question

    A couple of thoughts:

    1. Use consistent naming conventions that are grounded in mnemonics. Though it's nice to be able to use 255 characters to name a view or table or column, it can be a real pain to retype every time.

    2. Examine your assumptions carefully every time you create a new view. Do you really need it? Is it possible to modify an existing view? Can you avoid creating the view and use something dynamic instead?

    3. Someone else suggested the use of various tools to mine through the sysobjects table and determine relationships and dependencies. Note that you can determine object dependencies from QA by drilling into the object from the object browser.

    4. Documentation. I don't do enough of this either.

    Regards,

    hmscott

  11. #11
    Join Date
    Aug 2003
    Posts
    328

    Re: Basic Question

    Thanks alot!

Posting Permissions

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