Skip to content

Instantly share code, notes, and snippets.

@ConstantineK

ConstantineK/test_db_doc.md

Last active August 12, 2020 15:22
Show Gist options
  • Save ConstantineK/a2fdcf8a249306e7c889e64d481260a6 to your computer and use it in GitHub Desktop.
Save ConstantineK/a2fdcf8a249306e7c889e64d481260a6 to your computer and use it in GitHub Desktop.
Download ZIP
testing out sp_doc
Raw
test_db_doc.md

AdventureWorksDW2016

Tables

Click to expand

dbo.AdventureWorksDWBuildVersion

Column Type Null Foreign Key Default Description
DBVersion NVARCHAR(50) yes
VersionDate DATETIME yes

Back to top

dbo.DatabaseLog

Column Type Null Foreign Key Default Description
DatabaseLogID INT no
PostTime DATETIME no
DatabaseUser SYSNAME(256) no
Event SYSNAME(256) no
Schema SYSNAME(256) yes
Object SYSNAME(256) yes
TSQL NVARCHAR(MAX) no
XmlEvent XML no

Back to top

dbo.DimAccount

Column Type Null Foreign Key Default Description
AccountKey INT no
ParentAccountKey INT yes dbo.DimAccount.AccountKey
AccountCodeAlternateKey INT yes
ParentAccountCodeAlternateKey INT yes
AccountDescription NVARCHAR(50) yes
AccountType NVARCHAR(50) yes
Operator NVARCHAR(50) yes
CustomMembers NVARCHAR(300) yes
ValueType NVARCHAR(50) yes
CustomMemberOptions NVARCHAR(200) yes

Back to top

dbo.DimCurrency

Column Type Null Foreign Key Default Description
CurrencyKey INT no
CurrencyAlternateKey NCHAR(3) no
CurrencyName NVARCHAR(50) no

Back to top

dbo.DimCustomer

Column Type Null Foreign Key Default Description
CustomerKey INT no
GeographyKey INT yes dbo.DimGeography.GeographyKey
CustomerAlternateKey NVARCHAR(15) no
Title NVARCHAR(8) yes
FirstName NVARCHAR(50) yes
MiddleName NVARCHAR(50) yes
LastName NVARCHAR(50) yes
NameStyle BIT yes
BirthDate DATE yes
MaritalStatus NCHAR(1) yes
Suffix NVARCHAR(10) yes
Gender NVARCHAR(1) yes
EmailAddress NVARCHAR(50) yes
YearlyIncome MONEY yes
TotalChildren TINYINT yes
NumberChildrenAtHome TINYINT yes
EnglishEducation NVARCHAR(40) yes
SpanishEducation NVARCHAR(40) yes
FrenchEducation NVARCHAR(40) yes
EnglishOccupation NVARCHAR(100) yes
SpanishOccupation NVARCHAR(100) yes
FrenchOccupation NVARCHAR(100) yes
HouseOwnerFlag NCHAR(1) yes
NumberCarsOwned TINYINT yes
AddressLine1 NVARCHAR(120) yes
AddressLine2 NVARCHAR(120) yes
Phone NVARCHAR(20) yes
DateFirstPurchase DATE yes
CommuteDistance NVARCHAR(15) yes

Back to top

dbo.DimDate

Column Type Null Foreign Key Default Description
DateKey INT no
FullDateAlternateKey DATE no
DayNumberOfWeek TINYINT no
EnglishDayNameOfWeek NVARCHAR(10) no
SpanishDayNameOfWeek NVARCHAR(10) no
FrenchDayNameOfWeek NVARCHAR(10) no
DayNumberOfMonth TINYINT no
DayNumberOfYear SMALLINT no
WeekNumberOfYear TINYINT no
EnglishMonthName NVARCHAR(10) no
SpanishMonthName NVARCHAR(10) no
FrenchMonthName NVARCHAR(10) no
MonthNumberOfYear TINYINT no
CalendarQuarter TINYINT no
CalendarYear SMALLINT no
CalendarSemester TINYINT no
FiscalQuarter TINYINT no
FiscalYear SMALLINT no
FiscalSemester TINYINT no

Back to top

dbo.DimDepartmentGroup

Column Type Null Foreign Key Default Description
DepartmentGroupKey INT no
ParentDepartmentGroupKey INT yes dbo.DimDepartmentGroup.DepartmentGroupKey
DepartmentGroupName NVARCHAR(50) yes

Back to top

dbo.DimEmployee

Column Type Null Foreign Key Default Description
EmployeeKey INT no
ParentEmployeeKey INT yes dbo.DimEmployee.EmployeeKey
EmployeeNationalIDAlternateKey NVARCHAR(15) yes
ParentEmployeeNationalIDAlternateKey NVARCHAR(15) yes
SalesTerritoryKey INT yes dbo.DimSalesTerritory.SalesTerritoryKey
FirstName NVARCHAR(50) no
LastName NVARCHAR(50) no
MiddleName NVARCHAR(50) yes
NameStyle BIT no
Title NVARCHAR(50) yes
HireDate DATE yes
BirthDate DATE yes
LoginID NVARCHAR(256) yes
EmailAddress NVARCHAR(50) yes
Phone NVARCHAR(25) yes
MaritalStatus NCHAR(1) yes
EmergencyContactName NVARCHAR(50) yes
EmergencyContactPhone NVARCHAR(25) yes
SalariedFlag BIT yes
Gender NCHAR(1) yes
PayFrequency TINYINT yes
BaseRate MONEY yes
VacationHours SMALLINT yes
SickLeaveHours SMALLINT yes
CurrentFlag BIT no
SalesPersonFlag BIT no
DepartmentName NVARCHAR(50) yes
StartDate DATE yes
EndDate DATE yes
Status NVARCHAR(50) yes
EmployeePhoto VARBINARY(MAX) yes

Back to top

dbo.DimGeography

Column Type Null Foreign Key Default Description
GeographyKey INT no
City NVARCHAR(30) yes
StateProvinceCode NVARCHAR(3) yes
StateProvinceName NVARCHAR(50) yes
CountryRegionCode NVARCHAR(3) yes
EnglishCountryRegionName NVARCHAR(50) yes
SpanishCountryRegionName NVARCHAR(50) yes
FrenchCountryRegionName NVARCHAR(50) yes
PostalCode NVARCHAR(15) yes
SalesTerritoryKey INT yes dbo.DimSalesTerritory.SalesTerritoryKey
IpAddressLocator NVARCHAR(15) yes

Back to top

dbo.DimOrganization

Column Type Null Foreign Key Default Description
OrganizationKey INT no
ParentOrganizationKey INT yes dbo.DimOrganization.OrganizationKey
PercentageOfOwnership NVARCHAR(16) yes
OrganizationName NVARCHAR(50) yes
CurrencyKey INT yes dbo.DimCurrency.CurrencyKey

Back to top

dbo.DimProduct

Column Type Null Foreign Key Default Description
ProductKey INT no
ProductAlternateKey NVARCHAR(25) yes
ProductSubcategoryKey INT yes dbo.DimProductSubcategory.ProductSubcategoryKey
WeightUnitMeasureCode NCHAR(3) yes
SizeUnitMeasureCode NCHAR(3) yes
EnglishProductName NVARCHAR(50) no
SpanishProductName NVARCHAR(50) no
FrenchProductName NVARCHAR(50) no
StandardCost MONEY yes
FinishedGoodsFlag BIT no
Color NVARCHAR(15) no
SafetyStockLevel SMALLINT yes
ReorderPoint SMALLINT yes
ListPrice MONEY yes
Size NVARCHAR(50) yes
SizeRange NVARCHAR(50) yes
Weight FLOAT yes
DaysToManufacture INT yes
ProductLine NCHAR(2) yes
DealerPrice MONEY yes
Class NCHAR(2) yes
Style NCHAR(2) yes
ModelName NVARCHAR(50) yes
LargePhoto VARBINARY(MAX) yes
EnglishDescription NVARCHAR(400) yes
FrenchDescription NVARCHAR(400) yes
ChineseDescription NVARCHAR(400) yes
ArabicDescription NVARCHAR(400) yes
HebrewDescription NVARCHAR(400) yes
ThaiDescription NVARCHAR(400) yes
GermanDescription NVARCHAR(400) yes
JapaneseDescription NVARCHAR(400) yes
TurkishDescription NVARCHAR(400) yes
StartDate DATETIME yes
EndDate DATETIME yes
Status NVARCHAR(7) yes

Back to top

dbo.DimProductCategory

Column Type Null Foreign Key Default Description
ProductCategoryKey INT no
ProductCategoryAlternateKey INT yes
EnglishProductCategoryName NVARCHAR(50) no
SpanishProductCategoryName NVARCHAR(50) no
FrenchProductCategoryName NVARCHAR(50) no

Back to top

dbo.DimProductSubcategory

Column Type Null Foreign Key Default Description
ProductSubcategoryKey INT no
ProductSubcategoryAlternateKey INT yes
EnglishProductSubcategoryName NVARCHAR(50) no
SpanishProductSubcategoryName NVARCHAR(50) no
FrenchProductSubcategoryName NVARCHAR(50) no
ProductCategoryKey INT yes dbo.DimProductCategory.ProductCategoryKey

Back to top

dbo.DimPromotion

Column Type Null Foreign Key Default Description
PromotionKey INT no
PromotionAlternateKey INT yes
EnglishPromotionName NVARCHAR(255) yes
SpanishPromotionName NVARCHAR(255) yes
FrenchPromotionName NVARCHAR(255) yes
DiscountPct FLOAT yes
EnglishPromotionType NVARCHAR(50) yes
SpanishPromotionType NVARCHAR(50) yes
FrenchPromotionType NVARCHAR(50) yes
EnglishPromotionCategory NVARCHAR(50) yes
SpanishPromotionCategory NVARCHAR(50) yes
FrenchPromotionCategory NVARCHAR(50) yes
StartDate DATETIME no
EndDate DATETIME yes
MinQty INT yes
MaxQty INT yes

Back to top

dbo.DimReseller

Column Type Null Foreign Key Default Description
ResellerKey INT no
GeographyKey INT yes dbo.DimGeography.GeographyKey
ResellerAlternateKey NVARCHAR(15) yes
Phone NVARCHAR(25) yes
BusinessType VARCHAR20 no
ResellerName NVARCHAR(50) no
NumberEmployees INT yes
OrderFrequency CHAR1 yes
OrderMonth TINYINT yes
FirstOrderYear INT yes
LastOrderYear INT yes
ProductLine NVARCHAR(50) yes
AddressLine1 NVARCHAR(60) yes
AddressLine2 NVARCHAR(60) yes
AnnualSales MONEY yes
BankName NVARCHAR(50) yes
MinPaymentType TINYINT yes
MinPaymentAmount MONEY yes
AnnualRevenue MONEY yes
YearOpened INT yes

Back to top

dbo.DimSalesReason

Column Type Null Foreign Key Default Description
SalesReasonKey INT no
SalesReasonAlternateKey INT no
SalesReasonName NVARCHAR(50) no
SalesReasonReasonType NVARCHAR(50) no

Back to top

dbo.DimSalesTerritory

Column Type Null Foreign Key Default Description
SalesTerritoryKey INT no
SalesTerritoryAlternateKey INT yes
SalesTerritoryRegion NVARCHAR(50) no
SalesTerritoryCountry NVARCHAR(50) no
SalesTerritoryGroup NVARCHAR(50) yes
SalesTerritoryImage VARBINARY(MAX) yes

Back to top

dbo.DimScenario

Column Type Null Foreign Key Default Description
ScenarioKey INT no
ScenarioName NVARCHAR(50) yes

Back to top

dbo.FactAdditionalInternationalProductDescription

Column Type Null Foreign Key Default Description
ProductKey INT no
CultureName NVARCHAR(50) no
ProductDescription NVARCHAR(MAX) no

Back to top

dbo.FactCallCenter

Column Type Null Foreign Key Default Description
FactCallCenterID INT no
DateKey INT no dbo.DimDate.DateKey
WageType NVARCHAR(15) no
Shift NVARCHAR(20) no
LevelOneOperators SMALLINT no
LevelTwoOperators SMALLINT no
TotalOperators SMALLINT no
Calls INT no
AutomaticResponses INT no
Orders INT no
IssuesRaised SMALLINT no
AverageTimePerIssue SMALLINT no
ServiceGrade FLOAT no
Date DATETIME yes

Back to top

dbo.FactCurrencyRate

Column Type Null Foreign Key Default Description
CurrencyKey INT no dbo.DimCurrency.CurrencyKey
DateKey INT no dbo.DimDate.DateKey
AverageRate FLOAT no
EndOfDayRate FLOAT no
Date DATETIME yes

Back to top

dbo.FactFinance

Column Type Null Foreign Key Default Description
FinanceKey INT no
DateKey INT no dbo.DimDate.DateKey
OrganizationKey INT no dbo.DimOrganization.OrganizationKey
DepartmentGroupKey INT no dbo.DimDepartmentGroup.DepartmentGroupKey
ScenarioKey INT no dbo.DimScenario.ScenarioKey
AccountKey INT no dbo.DimAccount.AccountKey
Amount FLOAT no
Date DATETIME yes

Back to top

dbo.FactInternetSales

Column Type Null Foreign Key Default Description
ProductKey INT no dbo.DimProduct.ProductKey
OrderDateKey INT no dbo.DimDate.DateKey
DueDateKey INT no dbo.DimDate.DateKey
ShipDateKey INT no dbo.DimDate.DateKey
CustomerKey INT no dbo.DimCustomer.CustomerKey
PromotionKey INT no dbo.DimPromotion.PromotionKey
CurrencyKey INT no dbo.DimCurrency.CurrencyKey
SalesTerritoryKey INT no dbo.DimSalesTerritory.SalesTerritoryKey
SalesOrderNumber NVARCHAR(20) no
SalesOrderLineNumber TINYINT no
RevisionNumber TINYINT no
OrderQuantity SMALLINT no
UnitPrice MONEY no
ExtendedAmount MONEY no
UnitPriceDiscountPct FLOAT no
DiscountAmount FLOAT no
ProductStandardCost MONEY no
TotalProductCost MONEY no
SalesAmount MONEY no
TaxAmt MONEY no
Freight MONEY no
CarrierTrackingNumber NVARCHAR(25) yes
CustomerPONumber NVARCHAR(25) yes
OrderDate DATETIME yes
DueDate DATETIME yes
ShipDate DATETIME yes

Back to top

dbo.FactInternetSalesReason

Column Type Null Foreign Key Default Description
SalesOrderNumber NVARCHAR(20) no dbo.FactInternetSales.SalesOrderNumber
SalesOrderLineNumber TINYINT no dbo.FactInternetSales.SalesOrderLineNumber
SalesReasonKey INT no dbo.DimSalesReason.SalesReasonKey

Back to top

dbo.FactProductInventory

Column Type Null Foreign Key Default Description
ProductKey INT no dbo.DimProduct.ProductKey
DateKey INT no dbo.DimDate.DateKey
MovementDate DATE no
UnitCost MONEY no
UnitsIn INT no
UnitsOut INT no
UnitsBalance INT no

Back to top

dbo.FactResellerSales

Column Type Null Foreign Key Default Description
ProductKey INT no dbo.DimProduct.ProductKey
OrderDateKey INT no dbo.DimDate.DateKey
DueDateKey INT no dbo.DimDate.DateKey
ShipDateKey INT no dbo.DimDate.DateKey
ResellerKey INT no dbo.DimReseller.ResellerKey
EmployeeKey INT no dbo.DimEmployee.EmployeeKey
PromotionKey INT no dbo.DimPromotion.PromotionKey
CurrencyKey INT no dbo.DimCurrency.CurrencyKey
SalesTerritoryKey INT no dbo.DimSalesTerritory.SalesTerritoryKey
SalesOrderNumber NVARCHAR(20) no
SalesOrderLineNumber TINYINT no
RevisionNumber TINYINT yes
OrderQuantity SMALLINT yes
UnitPrice MONEY yes
ExtendedAmount MONEY yes
UnitPriceDiscountPct FLOAT yes
DiscountAmount FLOAT yes
ProductStandardCost MONEY yes
TotalProductCost MONEY yes
SalesAmount MONEY yes
TaxAmt MONEY yes
Freight MONEY yes
CarrierTrackingNumber NVARCHAR(25) yes
CustomerPONumber NVARCHAR(25) yes
OrderDate DATETIME yes
DueDate DATETIME yes
ShipDate DATETIME yes

Back to top

dbo.FactSalesQuota

Column Type Null Foreign Key Default Description
SalesQuotaKey INT no
EmployeeKey INT no dbo.DimEmployee.EmployeeKey
DateKey INT no dbo.DimDate.DateKey
CalendarYear SMALLINT no
CalendarQuarter TINYINT no
SalesAmountQuota MONEY no
Date DATETIME yes

Back to top

dbo.FactSurveyResponse

Column Type Null Foreign Key Default Description
SurveyResponseKey INT no
DateKey INT no dbo.DimDate.DateKey
CustomerKey INT no dbo.DimCustomer.CustomerKey
ProductCategoryKey INT no
EnglishProductCategoryName NVARCHAR(50) no
ProductSubcategoryKey INT no
EnglishProductSubcategoryName NVARCHAR(50) no
Date DATETIME yes

Back to top

dbo.NewFactCurrencyRate

Column Type Null Foreign Key Default Description
AverageRate REAL yes
CurrencyID NVARCHAR(3) yes
CurrencyDate DATE yes
EndOfDayRate REAL yes
CurrencyKey INT yes
DateKey INT yes

Back to top

dbo.ProspectiveBuyer

Column Type Null Foreign Key Default Description
ProspectiveBuyerKey INT no
ProspectAlternateKey NVARCHAR(15) yes
FirstName NVARCHAR(50) yes
MiddleName NVARCHAR(50) yes
LastName NVARCHAR(50) yes
BirthDate DATETIME yes
MaritalStatus NCHAR(1) yes
Gender NVARCHAR(1) yes
EmailAddress NVARCHAR(50) yes
YearlyIncome MONEY yes
TotalChildren TINYINT yes
NumberChildrenAtHome TINYINT yes
Education NVARCHAR(40) yes
Occupation NVARCHAR(100) yes
HouseOwnerFlag NCHAR(1) yes
NumberCarsOwned TINYINT yes
AddressLine1 NVARCHAR(120) yes
AddressLine2 NVARCHAR(120) yes
City NVARCHAR(30) yes
StateProvinceCode NVARCHAR(3) yes
PostalCode NVARCHAR(15) yes
Phone NVARCHAR(20) yes
Salutation NVARCHAR(8) yes
Unknown INT yes

Back to top

dbo.sysdiagrams

1

Column Type Null Foreign Key Default Description
name SYSNAME(256) no
principal_id INT no
diagram_id INT no
version INT yes
definition VARBINARY(MAX) yes

Back to top

Views

Click to expand * [dbo.vAssocSeqLineItems](#dbovassocseqlineitems) * [dbo.vAssocSeqOrders](#dbovassocseqorders) * [dbo.vDMPrep](#dbovdmprep) * [dbo.vTargetMail](#dbovtargetmail) * [dbo.vTimeSeries](#dbovtimeseries)

dbo.vAssocSeqLineItems

Column Type Null Description
OrderNumber NVARCHAR(20) no
LineNumber TINYINT no
Model NVARCHAR(50) yes

Definition

Click to expand ```sqlCREATE VIEW [dbo].[vAssocSeqLineItems] AS SELECT OrderNumber, LineNumber, Model FROM dbo.vDMPrep WHERE (FiscalYear = '2013') ```

Back to top

dbo.vAssocSeqOrders

Column Type Null Description
OrderNumber NVARCHAR(20) no
CustomerKey INT no
Region NVARCHAR(50) yes
IncomeGroup VARCHAR8 no

Definition

Click to expand ```sql/* vAssocSeqOrders supports assocation and sequence clustering data mmining models. - Limits data to FY2004. - Creates order case table and line item nested table.*/ CREATE VIEW [dbo].[vAssocSeqOrders] AS SELECT DISTINCT OrderNumber, ```

Back to top

dbo.vDMPrep

Column Type Null Description
EnglishProductCategoryName NVARCHAR(50) no
Model NVARCHAR(50) yes
CustomerKey INT no
Region NVARCHAR(50) yes
Age INT yes
IncomeGroup VARCHAR8 no
CalendarYear SMALLINT no
FiscalYear SMALLINT no
Month TINYINT no
OrderNumber NVARCHAR(20) no
LineNumber TINYINT no
Quantity SMALLINT no
Amount MONEY no

Definition

Click to expand ```sql

-- vDMPrep will be used as a data source by the other data mining views.
-- Uses DW data at customer, product, day, etc. granularity and -- gets region, model, year, month, etc. CREATE VIEW [dbo].[vDMPrep] AS SELECT pc.[Englis


</details>

[Back to top](#adventureworksdw2016)

### dbo.vTargetMail

| Column | Type | Null | Description |
| --- | ---| --- | --- |
CustomerKey | INT | no |  |
GeographyKey | INT | yes |  |
CustomerAlternateKey | NVARCHAR(15) | no |  |
Title | NVARCHAR(8) | yes |  |
FirstName | NVARCHAR(50) | yes |  |
MiddleName | NVARCHAR(50) | yes |  |
LastName | NVARCHAR(50) | yes |  |
NameStyle | BIT | yes |  |
BirthDate | DATE | yes |  |
MaritalStatus | NCHAR(1) | yes |  |
Suffix | NVARCHAR(10) | yes |  |
Gender | NVARCHAR(1) | yes |  |
EmailAddress | NVARCHAR(50) | yes |  |
YearlyIncome | MONEY | yes |  |
TotalChildren | TINYINT | yes |  |
NumberChildrenAtHome | TINYINT | yes |  |
EnglishEducation | NVARCHAR(40) | yes |  |
SpanishEducation | NVARCHAR(40) | yes |  |
FrenchEducation | NVARCHAR(40) | yes |  |
EnglishOccupation | NVARCHAR(100) | yes |  |
SpanishOccupation | NVARCHAR(100) | yes |  |
FrenchOccupation | NVARCHAR(100) | yes |  |
HouseOwnerFlag | NCHAR(1) | yes |  |
NumberCarsOwned | TINYINT | yes |  |
AddressLine1 | NVARCHAR(120) | yes |  |
AddressLine2 | NVARCHAR(120) | yes |  |
Phone | NVARCHAR(20) | yes |  |
DateFirstPurchase | DATE | yes |  |
CommuteDistance | NVARCHAR(15) | yes |  |
Region | NVARCHAR(50) | yes |  |
Age | INT | yes |  |
BikeBuyer | INT | no |  |

#### Definition

<details><summary>Click to expand</summary>
```sql
-- vTargetMail supports targeted mailing data model
-- Uses vDMPrep to determine if a customer buys a bike and joins to DimCustomer
CREATE VIEW [dbo].[vTargetMail] 
AS
    SELECT
        c.[CustomerKey], 
        c.[GeographyKey], 
        c

Back to top

dbo.vTimeSeries

Column Type Null Description
ModelRegion NVARCHAR(56) yes
TimeIndex INT yes
Quantity INT yes
Amount MONEY yes
CalendarYear SMALLINT no
Month TINYINT no
ReportingDate DATETIME yes

Definition

Click to expand ```sql

-- vTimeSeries view supports the creation of time series data mining models. -- - Replaces earlier bike models with successor models. -- - Abbreviates model names to improve readability in mining model viewer -- - Concatenates m


</details>

[Back to top](#adventureworksdw2016)

</details>

## Stored Procedures

<details><summary>Click to expand</summary>
* [dbo.generate_schema_graph](#dbogenerate_schema_graph)
* [dbo.generate_table_graph](#dbogenerate_table_graph)
* [dbo.sp_doc](#dbosp_doc)

### dbo.generate_schema_graph

| Parameter | Type | Output |
| --- | --- | --- |
@schema_name | NVARCHAR(128) | no |

#### Definition

<details><summary>Click to expand</summary>

```sqlcreate   procedure dbo.generate_schema_graph
(
	@schema_name nvarchar(128)
)
as
begin
select 1
end

Back to top

dbo.generate_table_graph

Parameter Type Output
@style NVARCHAR(MAX) no
@color NVARCHAR(MAX) no
@node NVARCHAR(MAX) no

Definition

Click to expand 
	digraph G {
	  subgraph cluster_1 {
		node [style=filled];
		"am I touching your butt" -> "pretty happy tbqh"
		label = "happiness decision matrix";
		color=blue
	  }
	  start -> "am I touching your butt";
	  "pretty happy tbqh" -> end;

	  st

Back to top

dbo.sp_doc

Parameter Type Output
@DatabaseName SYSNAME(256) no
@ExtendedPropertyName SYSNAME(256) no
@SqlMajorVersion TINYINT no
@SqlMinorVersion SMALLINT no

Definition

Click to expand 
CREATE PROCEDURE [dbo].[sp_doc]
	@DatabaseName SYSNAME = NULL
	,@ExtendedPropertyName SYSNAME = 'Description'
    /* Parameters defined here for testing only */
    ,@SqlMajorVersion TINYINT           = 0
    ,@SqlMinorVersion SMALLINT

Back to top

Scalar Functions

Click to expand * [dbo.udfBuildISO8601Date](#dboudfbuildiso8601date) * [dbo.udfMinimumDate](#dboudfminimumdate) * [dbo.udfTwoDigitZeroFill](#dboudftwodigitzerofill)

dbo.udfBuildISO8601Date

Parameter Type Output
Output DATETIME yes
@year INT no
@month INT no
@day INT no

Definition

Click to expand 
-- ******************************************************
-- Create User Defined Functions
-- ******************************************************
-- Builds an ISO 8601 format date from a year, month, and day specified as integers.
-- T

Back to top

dbo.udfMinimumDate

Parameter Type Output
Output DATETIME yes
@x DATETIME no
@y DATETIME no

Definition

Click to expand 
CREATE FUNCTION [dbo].[udfMinimumDate] (
    @x DATETIME, 
    @y DATETIME
) RETURNS DATETIME
AS
BEGIN
    DECLARE @z DATETIME

    IF @x <= @y 
        SET @z = @x 
    ELSE 
        SET @z = @y

    RETURN(@z)
END;

Back to top

dbo.udfTwoDigitZeroFill

Parameter Type Output
Output CHAR2 yes
@number INT no

Definition

Click to expand 
-- Converts the specified integer (which should be < 100 and > -1)
-- into a two character string, zero filling from the left 
-- if the number is < 10.
CREATE FUNCTION [dbo].[udfTwoDigitZeroFill] (@number int) 
RETURNS char(2)
AS
BEGIN
	D

Back to top

Table Functions

Click to expand

Synonyms

Click to expand

Markdown generated by sp_doc at 2020-08-12 08:21:57.9878128 -07:00.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment