Formatting a View (sp_addextendedproperty)?
-
I have been asked to create monthly reports summarizing some data in one of our databases. The previous DBA ran this and I suspect he had some automatted scripts, but people do not appear to document things here...
I stumbled on a view today but cannot figure out what is suppose to do or how to view it (other than the standard select *....) and I suspect it has been setup to output in a certain format (creative use of TOP to be able to use a GROUP BY, IMO - I think the previous DBA was much much more senior than he was given credit for!!!).
CREATE VIEW [dbo].[Total_View]
AS
SELECT TOP 100 PERCENT UserID, ColA, ColB, COUNT(*) AS TotalCt
FROM dbo.Info
GROUP BY ColA, ColB, UserID
ORDER BY ColB
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties =
Begin PaneConfigurations =
Begin PaneConfiguration = 0
NumPanes = 4
Configuration = "(H (1[53] 4[18] 2[10] 3) )"
End
Begin PaneConfiguration = 1
NumPanes = 3
Configuration = "(H (1 [50] 4 [25] 3))"
End
Begin PaneConfiguration = 2
NumPanes = 3
Configuration = "(H (1[50] 2[25] 3) )"
End
Begin PaneConfiguration = 3
NumPanes = 3
Configuration = "(H (4 [30] 2 [40] 3))"
End
Begin PaneConfiguration = 4
NumPanes = 2
Configuration = "(H (1 [56] 3))"
End
Begin PaneConfiguration = 5
NumPanes = 2
Configuration = "(H (2 [66] 3))"
End
Begin PaneConfiguration = 6
NumPanes = 2
Configuration = "(H (4 [50] 3))"
End
Begin PaneConfiguration = 7
NumPanes = 1
Configuration = "(V (3))"
End
Begin PaneConfiguration = 8
NumPanes = 3
Configuration = "(H (1 [56] 4 [18] 2))"
End
Begin PaneConfiguration = 9
NumPanes = 2
Configuration = "(H (1 [75] 4))"
End
Begin PaneConfiguration = 10
NumPanes = 2
Configuration = "(H (1[66] 2) )"
End
Begin PaneConfiguration = 11
NumPanes = 2
Configuration = "(H (4 [60] 2))"
End
Begin PaneConfiguration = 12
NumPanes = 1
Configuration = "(H (1) )"
End
Begin PaneConfiguration = 13
NumPanes = 1
Configuration = "(V (4))"
End
Begin PaneConfiguration = 14
NumPanes = 1
Configuration = "(V (2))"
End
ActivePaneConfig = 0
End
Begin DiagramPane =
Begin Origin =
Top = 0
Left = 0
End
Begin Tables =
Begin Table = "Parcels"
Begin Extent =
Top = 6
Left = 151
Bottom = 264
Right = 329
End
DisplayFlags = 280
TopColumn = 0
End
End
End
Begin SQLPane =
End
Begin DataPane =
Begin ParameterDefaults = ""
End
RowHeights = 220
End
Begin CriteriaPane =
Begin ColumnWidths = 12
Column = 1440
Alias = 900
Table = 1170
Output = 720
Append = 1400
NewValue = 1170
SortType = 1350
SortOrder = 1410
GroupBy = 1350
Filter = 1965
Or = 1350
Or = 1350
Or = 1350
End
End
End
' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'VIEW', @level1name=N'Total_View'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'VIEW', @level1name=N'Total_View'
Does anyone know how to view this in the intended format? Any ideas what format it is intended for? Or am I making this more complicated than it already is?
Thanks!
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
-
No reason to be scared. What you are seeing and posted is just the the CREATE script for the view. The main part of it is the SELECT TOP 100...GROUP...ORDER BY, the rest is just internal design to show the query results and bla bla.
If you right click on the view on the Object Browser and go to the Extended Properties you can copy and paste its value and you will see the code above. Not created by the "expert/senior" DBA, by the way
What exactly you need to summarize?.
-
Camilo, Thanks for your response.
I was hoping the "sp_addextendedproperty" was going to be a formatting method for me to just somehow query the view's contents and present it in a format I could hand Management. Basically, I was hoping to create a report as follows:
Airline Company 1 Total Passengers OnTime Late % Late
Airline Company 2 Total Passengers OnTime Late % Late
Airline Company 3 Total Passengers OnTime Late % Late
I have been manually running the queries and just copying the data (drag and drop) into Excel.
As for the "creative" part, I was just impressed the previous DBA used "TOP 100 PERCENT" to get all of the data and use an "ORDER BY" in a View.
Re-reading the BOL for "sp_addextendedproperty", I believe the code just sets the formatting for the columns when they are displayed. Looks like XML and possibly ReportServer are the next options...
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
-
sp_addextendedproperty is for putting descriptions, etc., on to your SQL server objects. It doesn't do any kind of formatting of any kind. It's for creating meta-data about your database objects. Say, for example, you want to add a property for the build number from source control so that you can always track the version of objects inside your database, or the name of the programmer or dba that created the object. There's a whole set of procedures around accessing, creating & deleting extended properties.
As to formatting columns, etc., from a view, what about:
SELECT tablename.columnname AS [Airline Company]
FROM tablename
One way to see what the code inside an object looks like is to use:
SELECT object_definition(OBJECT_ID(N'dbo.[all_supplier_view]'))
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
btw, your view has a bug in it, but it's a very common one.
you should not have ORDER BY clauses in a view, nor should you have the TOP 100 PERCENT hack that allows you to put the ORDER BY in. The ORDER BY belongs in the query that targets the view, not in the view definition itself.
The query optimizer reserves the right to ignore the ORDER BY clause if it sees fit, so if you rely on it you can get into trouble. See: http://blogs.msdn.com/sqltips/archive/2005/07/20/441053.aspx
---------------------------------------
elsasoft.org -
I have a similar view which is linked to a store procedure to bring data in a table. It runs and eats al the memory eventually getting terminated by the dba. He has advised me to simplify the view any helps please
CREATE VIEW [dbo].[VW_DTIRIS_Branck_Mngr_Hierarchy]
AS
SELECT DISTINCT
--TOP (100) PERCENT
p.OrgUnitManager, p.OrgUnitID, p.OrgUnitName, p.ParentID AS CHILD1_ParentID, C.OrgUnitManager AS CHILD1_OrgUnitManager,
C.OrgUnitID AS CHILD1_OrgUnitID, C.OrgUnitName AS CHILD1_OrgUnitName, C2.OrgUnitManager AS CHILD2_OrgUnitManager, C2.OrgUnitID AS CHILD2_OrgUnitID,
C2.OrgUnitName AS CHILD2_OrgUnitName, C3.OrgUnitManager AS CHILD3_OrgUnitManager, C3.OrgUnitID AS CHILD3_OrgUnitID,
C3.OrgUnitName AS CHILD3_OrgUnitName, C4.OrgUnitID AS CHILD4_OrgUnitID, C4.OrgUnitName AS CHILD4_OrgUnitName,
C4.OrgUnitManager AS CHILD4_OrgUnitManager, C5.OrgUnitID AS CHILD5_OrgUnitID, C5.OrgUnitName AS CHILD5_OrgUnitName,
C5.OrgUnitManager AS CHILD5_OrgUnitManager, C6.OrgUnitName AS CHILD6_OrgUnitName, C6.OrgUnitManager AS CHILD6_OrgUnitManager,
C6.OrgUnitID AS CHILD6_OrgUnitID, C7.OrgUnitID AS CHILD7_OrgUnitID, C7.OrgUnitName AS CHILD7_OrgUnitName, C7.OrgUnitManager AS CHILD7_OrgUnitManager,
C8.OrgUnitID AS CHILD8_OrgUnitID, C8.OrgUnitName AS CHILD8_OrgUnitName, C8.OrgUnitManager AS CHILD8_OrgUnitManager, C9.OrgUnitID AS CHILD9_OrgUnitID,
C9.OrgUnitName AS CHILD9_OrgUnitName, C9.OrgUnitManager AS CHILD9_OrgUnitManager, C10.OrgUnitID AS CHILD10_OrgUnitID,
C10.OrgUnitName AS CHILD10_OrgUnitName, C10.OrgUnitManager AS CHILD10_OrgUnitManager, C11.OrgUnitID AS CHILD11_OrgUnitID,
C11.OrgUnitName AS CHILD11_OrgUnitName, C11.OrgUnitManager AS CHILD11_OrgUnitManager
FROM dbo.VW_DIM_Current_OrgStructure AS C6 LEFT OUTER JOIN
dbo.VW_DIM_Current_OrgStructure AS C7 ON C6.OrgUnitID = C7.ParentID RIGHT OUTER JOIN
dbo.VW_DIM_Current_OrgStructure AS C5 ON C6.ParentID = C5.OrgUnitID RIGHT OUTER JOIN
dbo.VW_DIM_Current_OrgStructure AS C4 RIGHT OUTER JOIN
dbo.VW_DIM_Current_OrgStructure AS C3 ON C4.ParentID = C3.OrgUnitID RIGHT OUTER JOIN
dbo.VW_DIM_Current_OrgStructure AS p LEFT OUTER JOIN
dbo.VW_DIM_Current_OrgStructure AS C ON p.OrgUnitID = C.ParentID LEFT OUTER JOIN
dbo.VW_DIM_Current_OrgStructure AS C2 ON C.OrgUnitID = C2.ParentID ON C3.ParentID = C2.OrgUnitID ON C5.ParentID = C4.OrgUnitID LEFT OUTER JOIN
dbo.VW_DIM_Current_OrgStructure AS C8 ON C7.OrgUnitID = C8.ParentID LEFT OUTER JOIN
dbo.VW_DIM_Current_OrgStructure AS C9 ON C8.OrgUnitID = C9.ParentID LEFT OUTER JOIN
dbo.VW_DIM_Current_OrgStructure AS C10 ON C9.OrgUnitID = C10.ParentID LEFT OUTER JOIN
dbo.VW_DIM_Current_OrgStructure AS C11 ON C10.OrgUnitID = C11.ParentID
--WHERE (p.OrgUnitManager IS NOT NULL)
--ORDER BY p.OrgUnitManager, CHILD1_OrgUnitManager, CHILD2_OrgUnitManager, CHILD3_OrgUnitManager
GO
-
Please post new questions in a new thread.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply