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.Make a table of your views.|||I thought about that and think that it is the best way. Thanks.|||Thanks.|||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.|||Like an Excel Sheet?
Thanks.|||Any type of modeling software like visio, rational, erwin ...|||Thats a good idea. Thanks.|||Or you can go NUTS...

Just mine the parent child relationships with 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|||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|||Thanks alot!