/*============================================================================
  File:     DMObjects.sql

  Summary:  This script creates 3 functions and a stored procedure
			used to interrogate the new DM objects. Original queries
			based on examples and scripts from Dan Winn, SQL PM.

  Date:     March 15, 2005

  SQL Server Version: 9.00.1090 (IDW13)
------------------------------------------------------------------------------
  Copyright (C) 2005 Kimberly L. Tripp, SYSolutions, Inc.
  All rights reserved.

  For more scripts and sample code, check out 
    http://www.SQLskills.com

  This script is intended only as a supplement to demos and lectures
  given by Kimberly L. Tripp.  
  
  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
============================================================================*/

USE master
go

-- Enumerate all of the DM Objects

SELECT name, type
FROM sys.system_objects 
WHERE name LIKE 'dm%'
ORDER BY name
go
 
-- Find out if the DM object is a DMV or a DMF
IF OBJECTPROPERTY(object_id(N'dbo.DMObjectType'), 'IsScalarFunction') = 1
	DROP FUNCTION dbo.DMObjectType
go

CREATE FUNCTION dbo.DMObjectType
	(@DMObjectName	sysname)
RETURNS char(2)
AS
BEGIN
RETURN
	(SELECT so.type
	FROM sys.system_objects AS so
	WHERE so.name = @DMObjectName)
END
go

-- If it's a function, find it's parameters...
IF OBJECTPROPERTY(object_id(N'dbo.DMFunctionParams'), 'IsInlineFunction') = 1
	DROP FUNCTION dbo.DMFunctionParams
go

CREATE FUNCTION DMFunctionParams
	(@DMObjectName	sysname)
RETURNS table
AS
RETURN
(SELECT so.name				AS [DMObjectName]
		, sp.parameter_id	AS [ParameterPosition]
		, sp.name			AS [ParameterName]
		, st.name			AS [DataType]
		, sp.max_length		AS [MaxLength]
		, sp.* 
	FROM sys.system_parameters AS sp
		JOIN sys.system_objects AS so
				ON sp.[object_id] = so.[object_id]
		JOIN sys.types AS st
				ON sp.user_type_id = st.user_type_id
	WHERE so.name = @DMObjectName)
go

-- List it's output columns...
IF OBJECTPROPERTY(object_id(N'dbo.DMObjectColumns'), 'IsInlineFunction') = 1
	DROP FUNCTION dbo.DMObjectColumns
go

CREATE FUNCTION dbo.DMObjectColumns
	(@DMObjectName	sysname)
RETURNS table
AS
RETURN
(SELECT	so.name			AS [DMObjectName]
		, sc.column_id	AS [ColumnPosition]
		, sc.name		AS [ColumnHeader]
		, st.name		AS [DataType]
		, sc.max_length AS [MaxLength]
		, sc.precision	AS [Precision]
		, sc.scale		AS [Scale]
	FROM sys.system_columns AS sc
		JOIN sys.system_objects AS so
			ON sc.[object_id] = so.[object_id]
		JOIN sys.types AS st
			ON sc.user_type_id = st.user_type_id
	WHERE so.name = @DMObjectName)
go

-- Bring it all together in a nice clean sp!
IF OBJECTPROPERTY(object_id(N'dbo.sp_GetDMObjectInfo'), 'IsProcedure') = 1
	DROP PROCEDURE dbo.sp_GetDMObjectInfo
go

CREATE PROCEDURE dbo.sp_GetDMObjectInfo
		(@DMObjectName	sysname)
AS
IF (dbo.DMObjectType(@DMObjectName) NOT IN ('IF', 'TF', 'V') OR dbo.DMObjectType(@DMObjectName) IS NULL)
BEGIN
	RAISERROR ('Object does not exist or is not a Dynamic Management Object.', 16, -1)
	RETURN
END

IF dbo.DMObjectType(@DMObjectName) = 'IF'
BEGIN
	SELECT 'Dynamic Management Function' AS [Dynamic Management Object Type]

	SELECT * FROM dbo.DMFunctionParams(@DMObjectName)
	ORDER BY ParameterPosition
END
IF dbo.DMObjectType(@DMObjectName) = 'TF'
BEGIN
	SELECT 'Dynamic Management Table-Valued Function' AS [Dynamic Management Object Type]

	SELECT * FROM dbo.DMFunctionParams(@DMObjectName)
	ORDER BY ParameterPosition
END
IF dbo.DMObjectType(@DMObjectName) = 'V'
BEGIN
	SELECT 'Dynamic Management View' AS [Dynamic Management Object Type]
END

SELECT * FROM dbo.DMObjectColumns(@DMObjectName) AS DMObj
ORDER BY DMObj.ColumnPosition
go

-- Find out if the DM object is a DMV or a DMF	
--SELECT dbo.DMObjectType(N'dm_db_index_physical_stats')

-- If it's a function, find it's parameters...
--SELECT * FROM dbo.DMFunctionParams(N'dm_db_index_physical_stats')
--ORDER BY ParameterPosition

-- List it's output columns...
--SELECT * FROM dbo.DMObjectColumns(N'dm_db_index_physical_stats')
--ORDER BY ColumnPosition

-- Bring it all together in a nice clean sp!
EXEC dbo.sp_GetDMObjectInfo N'dm_db_index_physical_stats'