Database Views Hierarchy
Feb 2, 2006Hello:
In my SQL Server databases I have complex related tables and views.
There is any tool which allow you to see (print) this complex hierarchy of
views and tables as hierarchical tree?
Thanks,
GB
ADVERTISEMENT
Determine Dependency Hierarchy Between Views
Jan 22, 2008
I want to get a list of views and there dependent views on an existing database. But when i ran this query it dint fetch certain views since there were no entries in sysdepends. But the views reference existed in sysobjects.
select distinct so.[name] as parent,so2.[name] as child
from sysobjects so
inner join sysdepends sd on so.id = sd.id AND So.xtype = 'V'
inner join sysobjects so2 on sd.depid = so2.id AND So2.xtype = 'V'
order by parent,child.
How could i resolve this issue or what is the best way to identify the dependency hierarchy between views.
This was tested on SQL SERVER 2000.
Using Hierarchy ID Data Type In Material Database
Nov 10, 2014How to use hierarchy id data type in Bill of material database.
The msdn example [URL] ....
Very Tricky Hierarchy For Dimesion From Relational Database
May 22, 2008I need to create a dimension with a hierchy derived from 3 other dimenions where one of the dimensions contains a recursive hierarchy.
eg:
In my RDBMS I have; a table "Entities" which contextualises geographic locations and (typical columns include and id and a description with values for: ID1=United Kingdon, ID2=France, ID3=Norway, ID4=Finland, ID5=Europe, ID6=Scandinavia, etc.), another table "Entity_Type" which contextualises the values held in "Entities" (Typical olumns; ID1=Continent, ID2=Country) and a table, "Geo_Code" which codifies the values from both "Entities" and "Entity_type". They act more or less like 2 dimensions ("Entities", Entity_Type) and a fact table ("GEO_Code").
The third table has a recursive hierarchy in that it references it's own ID again in column "Entity Reference".
The first table is Entities, the second is Entity_Type and the third is the one that references both.
id
description
01
England
02
Norway
03
Europe
04
Scandinavia
id
description
01
Continent
02
Country
id
entity
Entity_Type
entity Reference
01
03
01
NULL
02
04
01
NULL
03
02
02
02
04
01
02
01
From the last table "GEO_CODE" we can determine the following:
01. Europe is a continent
02. Scandinavia is a continent
03. Norway is a Country that is in Scandinavia (Entity Reference recursive referencing ID 02 of the same table where the Entity is 04).
04. England is a Country that is Europe (Entity Reference recursive referencing ID 01 of the same table where the Entity is 03).
How do I create a new dimension using Visual Studio in Analysis Services where the Hierarchy is reflected?
So far, I have managed to depict 2 Hierarchies: where a dimenion depicts Entity (Countinent/Country) as level 1 and the value of the corresponding entity (Europe, ENgland, Norway, Scandinavia) in level 2 but how do I get to the last bit where the Entity Reference references the location of the Country or Continent???
Are Embedded Views (Views Within Views...) Evil And If So Why?
Apr 3, 2006Fellow database developers,I would like to draw on your experience with views. I have a databasethat includes many views. Sometimes, views contains other views, andthose views in turn may contain views. In fact, I have some views inmy database that are a product of nested views of up to 6 levels deep!The reason we did this was.1. Object-oriented in nature. Makes it easy to work with them.2. Changing an underlying view (adding new fields, removing etc),automatically the higher up views inherit this new information. Thismake maintenance very easy.3. These nested views are only ever used for the reporting side of ourapplication, not for the day-to-day database use by the application.We use Crystal Reports and Crystal is smart enough (can't believe Ijust said that about Crystal) to only pull back the fields that arebeing accessed by the report. In other words, Crystal will issue aSelect field1, field2, field3 from ReportingView Where .... eventhough "ReportingView" contains a long list of fields.Problems I can see.1. Parent views generally use "Select * From childview". This meansthat we have to execute a "sp_refreshview" command against all viewswhenever child views are altered.2. Parent views return a lot of information that isn't necessarilyused.3. Makes it harder to track down exactly where the information iscoming from. You have to drill right through to the child view to seethe raw table joins etc.Does anyone have any comments on this database design? I would love tohear your opinions and tales from the trenches.Best regards,Rod.
View 15 Replies View RelatedHiding Database Views
Oct 17, 2001Help Please!
How can I hide databases from users that do not have permission to log onto them in the SQL Enterprise Manager.
Thanks in advance for the help.
-Rich
Run 1 Query Against All Views In Database
Jul 16, 2014In SQL server 2008 how can I run the same query against each view in that database?
One database about 75 views.
Or even something like
Select 'yes' from view
Where shipping_status = 'delayed'
And return a list of view names so I know which views to query against.
I ran
Code:
select name from sysobjects where type = v
And got a list of my views. How can I now run my SQL statement against the list to see which views contain the info?
Select From Database Without Grid Views Etc
Jul 9, 2006Hi,
I want to be able to select data from a database without using grid
view etc. I can set up a connection and bind it to a grid view but I
would like to do some data processing without showing it. Something like
select id from table where name = "x"
store id in idvar
store the row count to a variable rowcount
if rowcount > 0
select name from table2 where id = idvar
endif
etc
I can see how to set the sqldatasource select command but not how to run it and get results out of it. Please help
Using One Database Call To Populate 8 Grid Views
Apr 23, 2008Hi
I'd like to be a little more efficient in my approach when using databases. Although my site is very simple with a low hit rate expected, I would still like to learn the best methods in designing server load conscious code.
Basically, I have a page where I'd like to show events broken down into days of the week (Monday - Sunday). All the events contain the same data and only difference being the day. Right now, the only way I know how to make this work is to create a unique sqlDataSource (select * where Day = Monday) then assign it to a ‘Monday’ grid view. I then repeat this same process for each day of the week.
As the data is always the same, is there a way to make only call to the database returning all the data, then close the connection and then separate the data there after. To me this seems to be a much more efficient way to manage the data???
Thanks for any help/supportRichard
Alter All Objects In A Database / Views / Triggers
Apr 20, 2015I am trying to replace object name in views , triggers, stored procs, UDF,TVF etc.I have created a automated script to replace 'dbo.Cust' with 'dbo.Customer' in all objects and generate script as ALTER Statements. some objects are still scripted out as Create. Reason is it has some extra space in between
CREATE TABLE #test1(
[NAME] [nvarchar](128) NOT NULL,
[DEFINITION] [nvarchar](max) NULL,
[DEFINITION_bk] [nvarchar](max) NULL,
[type] [char](2) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
[code]....
No Views After Creating A Database With Restore From Backup
Nov 13, 2007Forgive me if I'm asking a simple question, but I'm new to database administration
I'm trying to migrate a database from a server running Windows 2000 Server and SQL Server 2000 to a new machine running Windows Server 2003 and SQL Server 2005. I moved a copy of a recent database backup file to the new server, and created a new database with Restore From Backup. The tables seem to have restored fine, but my views are non-existent. Has anyone seen this problem before, or does anyone know of something I may have skipped?
Is There A Way To Give Someone The Right To Create Only Views And No Other Objects In A Database
Feb 25, 2008Is there a way to give someone the right to create only views and no other objects in a database? Currently I have given the individual ddladmin database rose, but would rather be more restrictive?
View 1 Replies View RelatedHow To Display List Of Tables && SP && Views In A Database?
Aug 16, 2007
Hi
In SQL Server 2000 & 2005
How to Display list of Tables & S.Ps & Views in a Database?
Please advise
Thanks
Searching The Keywords In A Collection Of Views Present In A Database
Jun 17, 2008Hi All,
i have some views in my database, and these views are having some columns,i want to know particular column name to be there in particular view.
For example ,just like functionality of sp_search_code 'Keyword'.
like this i want to search in views .
or else please let me know sp_search_code 'Keyword'. was used for views also.
Thanks and Regards,
G.JaganMohanrao.
How Can I Get The List Of Tables And Views Together In The Database By The Dbconnection.getSchema()
May 3, 2006I have tried SqlConnection.getSchema("Tables"). it returns me a list of all tables while getSchema("Views") returns me the views in the form of DataTable. But can we have a way to get them together in the same DataTable as a return result? because I want to read the rows by DataTable.CreateDataReader() later.
View 2 Replies View RelatedAvoid Cross Database Views In Data Warehouse
May 4, 2007We have a relational database (rd) and a data warehouse (dw). This dw has a table (tw) with all key fields (dimension keys) and metric related (measures) fields. This table is populated with monthly data each month. The tw is joined to various look up views present in the dw to obtain name fields from rd. The DBA wants me to remove the look up views. I now have following 2 options that I can think of –
1)Further de-normalize the tw and store the name fields as well. However, there are two issues with this option –
a.The size of tw will grow tremendously.
b.We are storing monthly data and the values in the name fields may change after some time. Then we will have to put in additional views/objects to obtain the latest name.
2)Using ETL, obtain the copy of rd tables overnight in dw. We will then join tw with these tables and there will no longer be cross database joins. However, this will be a burden on maintenance and support.
As of now these are the possible options I can come up with. Which one would you suggest and why? If you have another option, please let me know.
Thank you all in advance,
sajmera
Script All Database Tables,views And Stored Proced
Jun 24, 2008what's the easiest way to script all table,views and stored procedures?
I would like to script them like from ssms i right click on each one and choose script table as --- but how can I do it for all of them at once?
Large Views Vs Multiple Small Views
Sep 6, 2007Which is more efficient? One large view that joins >=10 tables, or a few smaller views that join only the tables needed for individual pages?
View 1 Replies View RelatedHow To Find Out The List Of All Tables And Views Owned By A Particular User In Any Database?
Oct 15, 2001How to find out the list of all tables and views owned by a particular user in any database by querying the system tables?Thanks.
View 2 Replies View RelatedGrant User Group Select Access To All Views In Database
Sep 24, 2013I'm trying to grant a user group select access to all the views in a database. I already made a query which creates a result set whit the SQL Syntax I (displayed below) need but it seems to be impossible to get this result set executed after creation.
use [AdventureWorksDW2008R2]
SELECT
'GRANT SELECT ON [' + SCHEMA_NAME(Schema_id) + '].[' + name + '] TO [DOMAINGROUP]'
FROM sys.views;
SQL Server Admin 2014 :: Script To Find Nested Views In A Database
Oct 7, 2015Any easy way to find if there are any nested views existing in the database?...using SQL server 2014...
View 1 Replies View RelatedSQL Server 2014 :: Query For Listing Views In A Database Involving Tables From Other Databases?
Oct 19, 2015script to get the list of views in a database, involving tables from other databases?I AM using SQL server 2014
View 2 Replies View RelatedRecompiling Views That Reference Other Views
Jun 28, 2007Hello.
Newbie here. I've only been using SQL for about a year now and have some minor questions about sql objects that reference other objects.
We have some views which reference other views in the joins. I will call one the primary view and the one being referenced in the joins as the secondary view.
Recently we made changes to the secondary view.
After which the primary views which referenced it would not work because of this change and had to be 'refreshed' by using drop/create scripts which essentially just dropped it and recreated the exact same view. I do not recall the exact error message that was returned other than it seemed to suggest that it could no longer see the secondary view since it had been changed. Nothing in the primary view was changed in any way, just the secondary.
Some here where I work have suggested off hand that this was a recompile of the primary view because the contents of the secondary changed.
My questions are:
1. Exactly why did this happen and is there a proper name for it when it does?
2. The same problem does not seem to occur when we have stored procedures referencing views in the joins which had just been changed. Why is that?
Thanks for any help on the matter. I greatly appreciate it.
Quicky : Views Of Views Of Views
Feb 22, 2007Hello,
to make a report easier I'm developing it using a view of joined views of joined views.
Is there any significant performance penalty as opposed to just having one big select?
Cheers.
Db Hierarchy
May 8, 2006HI!
I am new in DB so I need some advices for finding the right solution.
I need to be able to make automatically any join between tables which the user choose and deliver the result.
is like making an hierarchy between all tables in DB (parent-child) and then making the select statement for the right join.
if u have any idea about how can I manage this, pls help me.
thanks a lot!
HIERARCHY HELP! PLEASE?
Feb 26, 2008I have an Interesting situation that I'm hoping some of you experts can help me with basically I have the following hierarchy:
Net Profit
.....Gross Profit
..........Revenue
..........Direct Costs
.....Indirect Expense
Now, I have another hierarchy - separate from the above - that I need to assimilate to the hierarchy above - Example:
Expenses
.....Node a
..........Child a (Indirect Expense Type)
.....Child b (Direct Cost type)
Net Sales Adjustments
.....Child a (Revenue type)
.....Child b (Revenue type)
Depending on the type, the top most node needs to be assigned to the appropriate node in the initial hierarcy, like this:
Net Profit
....Gross Profit
........Revenue
................Net Sales Adjustments
....................Child a (Revenue type)
....................Child b (Revenue type)
........Direct Costs
...........Expenses
.................Child b (Direct Cost type)
........Indirect Expense
............Expenses
.................Node a
.....................Child a (Indirect Expense Type)
Now, I've been able to figure out how to assign the top most node and leafs if all the children have the same type (using the expan stored proc listed in the books online), but my question is on this portion:
Expenses
.....Node a
...........Child a (Indirect Expense Type)
.....Child b (Direct Cost type)
Basically, the answer is to work backwards - if a child has a type that is different than the other children, a copy of the hierarch (up to the child) needs to be made and assigned to the appropriate initial node.
I've tried modifying the expand stored proc to give me the lineage of the child and see if there's a way I can copy the node and place it appropriately - I'm brainfried at this point, and I'm hoping that someone outthere can point me in the right direction.
Thanks in advance for your time
Hierarchy
Mar 7, 2008Hai everyone.,
i need to get solution for tree hierarchy in sql is there any solution or any keyword like 'connect by prior' in oracle ..
plz help me on this..
for example:
id | FName |parentid |
1 | sandy |
2 | robert| 1
if i give the parentid 1 in where condition of a query i need the details of 'sandy'
Thanks in Advance.
B.Arul.
Hierarchy
Jul 10, 2006Hello!I have a table that looks like this:Col1; Col2; Col3; Col4; Col538; 75; 233; 916; 277038; 75; 233; 916; 277138; 75; 233; 916; 277238; 75; 233; 923; 265438; 75; 233; 923; 265538; 75; 245; 913; 245438; 75; 245; 913; 2456....And I need a query (not a procedure) that shows me this:38; NULL; NULL; NULL; NULLNULL; 75; NULL; NULL; NULLNULL; NULL; 233; NULL; NULLNULL; NULL; NULL; 916; NULLNULL; NULL; NULL; NULL; 2770NULL; NULL; NULL; NULL; 2771NULL; NULL; NULL; NULL; 2772NULL; NULL; NULL; 923; NULLNULL; NULL; NULL; NULL; 2654NULL; NULL; NULL; NULL; 2655NULL; NULL; 245; NULL; NULLNULL; NULL; NULL; 913; NULLNULL; NULL; NULL; NULL; 2454NULL; NULL; NULL; NULL; 2456....Does anybody know how i can get this result? How?Help! Thank you!SQLNullps: SQL-Server 2000
View 3 Replies View RelatedHierarchy
May 24, 2006Hi to All!
Is there a perfect method to implement a hierarchy structure with different types as a table in Sql server 2005? Currently I am thinking of this way:
[Node | ParentId | ParentType | ChildId | ChildType]
But there is this nagging little voice saying it can be better
Cheers!
Nele
Hierarchy ORBER BY
Mar 9, 2004Hi, I'm trying to figure out how to write I SP on the following table that will produce the results show at the bottom. My main problem is the sorting, is there a way I loop through the records in TSQL and ORDER BY SortSort where the ParentID's are the same?
Thank you.
Table example
IDParentIDSortOrderCaption
1null1Main
211Files
313Folders
422File2
512Holders
621File1
752Holder2
851Holder1
Required result example
Main
-Files
--File1
--File2
-Holders
--Holder1
--Holder2
-Folders
Hierarchy Level
Apr 28, 2008Hi
i tried on CTE but iam not gettng correct result plz help me
Declare @TMaster Table (MasterId int, Type varchar(100), ParentMasterId int)
Insert into @Tmaster
Select 1, 'Fixed', 14 Union All
Select 2, 'Flexible',14 union All
Select 3, 'Others',14 union All
Select 13, 'Retirement benefits',1 union All
Select 14, 'PBHeads', 0
Select * From @Tmaster
Declare @TDetails Table ( MasterId Int, Description varchar(100))
Insert into @TDetails
Select 1, 'Basic' union all
Select 1,'Conveyance' union all
Select 1,'HRA' union all
Select 1,'Special Allowance' union all
Select 2, 'Children Education' union all
Select 2,'Travel Allowance' union all
Select 2,'Medical Expenses' union all
Select 2,'Variable Spl Pay' union all
Select 3, 'Project Allowance' union all
Select 3,'Quarterly Incentive' union all
Select 3,'Shift Allowance' union all
Select 3,'Annual Component' union all
Select 13,'Provident Fund' union all
Select 13,'Gratuity' union all
Select 13,'Super annuation'
Select * From @TDetails
in group list we have to show fixed, flexible, other
in subgroups(L1) list under fixed we have to show
Basic salary, HRA, Conveyance, Special Allowance,Retirement benefits
in subgroup(L2) we have to show Gratuity, providedfund, Super annuation they are subgroups of retirementbenefits
output :
Group Subgroup(L1) Subgroup(L2)
Fixed
Basic salary
HRA
Conveyance
Special Allowance
Retirement benefits
Provident Fund
Gratuity
Super annuation
Flexible reimbursements
LTA/Group Travel
Medical expenses
Children education
variable special pay
Others
Project allowance
Performance incentive
Medical insurance
Ordering Within Hierarchy
Aug 7, 2007Hi all,
Ive got a bit of a problem,
I have two tables:
CATEGORY
id
name
parent_CATEGORY_id
RECORD
id
CATEGORY_id
Stock_Held_Number
ps. Ive stripped out any non relevant fields
I also have the following query (again ive stripped out the non-relevant fields)
SELECT TOP (100) PERCENT SUM(dbo.RECORD.Stock_Held_Number) AS TotalStock, CATEGORY.Name AS FundName
FROM CATEGORY
LEFT OUTER JOIN dbo.RECORD ON CATEGORY.ID = dbo.RECORD.CATEGORY_id
GROUP BY CATEGORY.Name, RECORD.Stock_Held_Number
At the moment its grouping all the CATEGORIES and giving me a sum total for each which is great.
The problem I have is the CATEGORY table, there is an optional join to parent CATEGORY records on the table.
What Im trying to do is to provide a fully ordered result within the CATEGORIES and I don't have a clue.
For example:
The CATEGORY table has the following values
ID Name Parent_Category_id
1 Pens
2 Animals
3 Rocks
4 Horses 2
5 Dogs 2
When I currently run the query I get:
Name TotalStock
Pens 20
Animals 30
Rocks 40
Horses 50
Dogs 60
It's technically correct because I don't want the parent to calculate the total value of the children
What Im really trying to do is order them within the hierarchy though and indent (if possible) the result so I would get
Name TotalStock
Animals 30
---Dogs 60
---Horses 50
Pens 20
Rocks 40
Does anybody have any pointers as to how I can achieve this.
Many thanks
Mike
Parameter Hierarchy
Feb 21, 2008how do i create a parameter in hierarchy?
SAMPLE
billing period:
YEAR 2007
1st quarter
jan
feb
mar
2nd quarter
april
may
june
something like that.... if the user chose the 1st quarter the report will generate a sum-up report for the 1st quarter of 2007...
kindly reply asap.. i really need to get this report working..
thanks!