Showing posts with label SQL 2008. Show all posts
Showing posts with label SQL 2008. Show all posts

Tuesday, January 27, 2015

How to list object dependencies ?

-- View column dependencies
SELECT ReferencingObject = SCHEMA_NAME(o1.schema_id) + '.' + o1.name,
       ReferencedObject = SCHEMA_NAME(o2.schema_id) + '.'
                          + ed.referenced_entity_name,
       ColumnName = c.name,
       ReferencedObjectType = o2.type,
       ReferencingObjecType = o1.type
FROM   AdventureWorks2012.sys.sql_expression_dependencies ed
       INNER JOIN AdventureWorks2012.sys.objects o1
               ON ed.referencing_id = o1.object_id
       INNER JOIN AdventureWorks2012.sys.objects o2
               ON ed.referenced_id = o2.object_id
       INNER JOIN AdventureWorks2012.sys.sql_dependencies d
               ON ed.referencing_id = d.object_id
                  AND d.referenced_major_id = ed.referenced_id
       INNER JOIN sys.columns c
               ON c.object_id = ed.referenced_id
                  AND d.referenced_minor_id = c.column_id
WHERE  SCHEMA_NAME(o1.schema_id) + '.' + o1.name = 'HumanResources.vEmployee'
ORDER  BY ReferencedObject,
          c.column_id; 

ReferencingObject
ReferencedObject
ColumnName
ReferencedObjectType
ReferencingObjecType
HumanResources.vEmployee
HumanResources.Employee
BusinessEntityID
U
V
HumanResources.vEmployee
HumanResources.Employee
JobTitle
U
V
HumanResources.vEmployee
Person.Address
AddressID
U
V
HumanResources.vEmployee
Person.Address
AddressLine1
U
V
HumanResources.vEmployee
Person.Address
AddressLine2
U
V
HumanResources.vEmployee
Person.Address
City
U
V
HumanResources.vEmployee
Person.Address
StateProvinceID
U
V
HumanResources.vEmployee
Person.Address
PostalCode
U
V
HumanResources.vEmployee
Person.BusinessEntityAddress
BusinessEntityID
U
V
HumanResources.vEmployee
Person.BusinessEntityAddress
AddressID
U
V
HumanResources.vEmployee
Person.CountryRegion
CountryRegionCode
U
V
HumanResources.vEmployee
Person.CountryRegion
Name
U
V
HumanResources.vEmployee
Person.EmailAddress
BusinessEntityID
U
V
HumanResources.vEmployee
Person.EmailAddress
EmailAddress
U
V
HumanResources.vEmployee
Person.Person
BusinessEntityID
U
V
HumanResources.vEmployee
Person.Person
Title
U
V
HumanResources.vEmployee
Person.Person
FirstName
U
V
HumanResources.vEmployee
Person.Person
MiddleName
U
V
HumanResources.vEmployee
Person.Person
LastName
U
V
HumanResources.vEmployee
Person.Person
Suffix
U
V
HumanResources.vEmployee
Person.Person
EmailPromotion
U
V
HumanResources.vEmployee
Person.Person
AdditionalContactInfo
U
V
HumanResources.vEmployee
Person.PersonPhone
BusinessEntityID
U
V
HumanResources.vEmployee
Person.PersonPhone
PhoneNumber
U
V
HumanResources.vEmployee
Person.PersonPhone
PhoneNumberTypeID
U
V
HumanResources.vEmployee
Person.PhoneNumberType
PhoneNumberTypeID
U
V
HumanResources.vEmployee
Person.PhoneNumberType
Name
U
V
HumanResources.vEmployee
Person.StateProvince
StateProvinceID
U
V
HumanResources.vEmployee
Person.StateProvince
CountryRegionCode
U
V
HumanResources.vEmployee
Person.StateProvince
Name
U
V




-- SQL Server 2008 object dependency query - listing object dependencies
SELECT ReferencingObjectType = o1.type,
       ReferencingObject = SCHEMA_NAME(o1.schema_id)+'.'+o1.name,
       ReferencedObject = SCHEMA_NAME(o2.schema_id)+'.'+ed.referenced_entity_name,
       ReferencedObjectType = o2.type
FROM   AdventureWorks2008.sys.sql_expression_dependencies ed
       INNER JOIN  AdventureWorks2008.sys.objects o1
         ON ed.referencing_id = o1.object_id
       INNER JOIN AdventureWorks2008.sys.objects o2
         ON ed.referenced_id = o2.object_id
WHERE o1.type in ('P','TR','V', 'TF')
ORDER BY ReferencingObjectType, ReferencingObject
-- Partial results
Type ReferencingObject ReferencedObject Type
P  dbo.uspGetBillOfMaterials Production.BillOfMaterials U 
P  dbo.uspGetBillOfMaterials Production.Product U 
P  dbo.uspGetEmployeeManagers HumanResources.Employee U 
P  dbo.uspGetEmployeeManagers Person.Person U 
P  dbo.uspGetManagerEmployees HumanResources.Employee U 
P  dbo.uspGetManagerEmployees Person.Person U 
P  dbo.uspGetWhereUsedProductID Production.BillOfMaterials U 
P  dbo.uspGetWhereUsedProductID Production.Product U 
P  dbo.uspLogError dbo.ErrorLog U 
P  dbo.uspLogError dbo.uspPrintError P 
------------

-- Find all stored procedures where a column is being used/referenced
USE AdventureWorks2008;
DECLARE @SchemaName sysname = N'Production';
DECLARE @TableName sysname = N'Product';
DECLARE @ColumnName sysname = N'ProductID';

SELECT QUOTENAME(refing.referencing_schema_name) +
     N'.' + QUOTENAME(refing.referencing_entity_name) As SprocName
FROM sys.dm_sql_referencing_entities(QUOTENAME(ISNULL(@SchemaName,N'dbo')) +
     N'.' + QUOTENAME(@TableName),'object') refing
CROSS APPLY sys.dm_sql_referenced_entities(QUOTENAME(refing.referencing_schema_name) +
     N'.' + QUOTENAME(refing.referencing_entity_name), 'object') refed
WHERE EXISTS(SELECT * FROM sys.objects 
             WHERE refing.referencing_id = object_id and type ='P')
  AND refed.referenced_schema_name = @SchemaName
  AND refed.referenced_entity_name = @TableName
  AND refed.referenced_minor_name  = @ColumnName
ORDER BY SprocName;
/*  SprocName
[dbo].[sprocGetWhereProductUsed]
[dbo].[uspGetBillOfMaterials]
[dbo].[uspGetWhereUsedProductID]
[dbo].[uspProductByColor]  */
------------

Source : sqlusa
Posted by at No comments:
Labels:

How to design table-valued parameters ?

-- SQL Server Table-Valued Parameters of functions and stored procedures
USE AdventureWorks2008;
GO
-- Create new table type
CREATE TYPE dbo.tpProdInfo AS TABLE(ProdID INT, ProdNbr char(12),
                                    ListPrice money, Color char(16));
GO

-- Create table-valued function(UDF) with table-valued parameter
CREATE FUNCTION tvfGroupByColor (@Input dbo.tpProdInfo READONLY)
RETURNS @Result TABLE (Color char(16), AvgListPrice money)
AS
BEGIN
  INSERT @Result
  SELECT Color, avg(ListPrice) FROM @Input
  GROUP BY Color
  RETURN
END
GO

-- Create stored procedure with table-valued parameter
CREATE PROCEDURE uspMinMaxPriceByColor @Input dbo.tpProdInfo READONLY
AS
BEGIN
  SELECT Color, MinPrice=min(ListPrice), MaxPrice=MAX(ListPrice)
  FROM @Input
  GROUP BY Color
  ORDER BY MaxPrice DESC
END
GO

-- Test TVF with table-valued parameter
DECLARE @PriceDetail dbo.tpProdInfo
INSERT @PriceDetail SELECT ProductID, ProductNumber, ListPrice, Color
                    FROM Production.Product WHERE Color is not null
SELECT * FROM tvfGroupByColor (@PriceDetail)
ORDER BY AvgListPrice DESC
GO
/*
Color             AvgListPrice
Red               1401.95
Yellow            959.0913
Blue              923.6792
Silver            850.3053
Black             725.121
Grey              125.00
Silver/Black      64.0185
Multi             59.865
White             9.245
*/

-- Test stored procedure with table-valued parameter
DECLARE @PriceDetail dbo.tpProdInfo
INSERT @PriceDetail SELECT ProductID, ProductNumber, ListPrice, Color
                    FROM Production.Product WHERE Color is not null
EXEC uspMinMaxPriceByColor @PriceDetail
GO
/*
Color             MinPrice    MaxPrice
Red               34.99       3578.27
Silver            0.00        3399.99
Black             0.00        3374.99
Blue              34.99       2384.07
Yellow            53.99       2384.07
Grey              125.00      125.00
Multi             8.99        89.99
Silver/Black      40.49       80.99
White             8.99        9.50
*/
-- Cleanup
DROP FUNCTION tvfGroupByColor
DROP PROC uspMinMaxPriceByColor
DROP TYPE dbo.tpProdInfo
GO
------------

Source : sqlusa
Posted by at No comments:
Labels:

How to convert HierarchyID to string ?

-- Convert HierarchyID to string and reverse
SELECT TOP 1000 [BusinessEntityID]
      ,[NationalIDNumber]
      ,[OrganizationNode]
      ,[OrganizationNode].ToString() as AlphaHierarchyID
      ,[OrganizationLevel]
      ,hierarchyid::Parse([OrganizationNode].ToString()+'1/') AS AlphaToHierarchyID
  FROM [AdventureWorks2008].[HumanResources].[Employee]
  ORDER BY BusinessEntityID
/*  BusinessEntityID    NationalIDNumber  OrganizationNode  AlphaHierarchyID 
                        OrganizationLevel AlphaToHierarchyID
1     295847284   0x    /     0     0x58
2     245797967   0x58  /1/   1     0x5AC0
3     509647174   0x5AC0      /1/1/ 2     0x5AD6
4     112457891   0x5AD6      /1/1/1/     3     0x5AD6B0
5     695256908   0x5ADA      /1/1/2/     3     0x5ADAB0
6     998320692   0x5ADE      /1/1/3/     3     0x5ADEB0
7     134969118   0x5AE1      /1/1/4/     3     0x5AE158
......*/
------------

Source : sqlusa
Posted by at No comments:
Labels:
Subscribe to: Posts (Atom)