How to use the object_definition system function?
Execute the following script in Query Editor to demonstratate the use of object_id, object_schema_name, object_name and object_definition system functions:
Execute the following script in Query Editor to demonstratate the use of object_id, object_schema_name, object_name and object_definition system functions:
/*
OBJECT_DEFINITION applies to the following object types:
C = Check constraint
D = Default
P = SQL stored procedure
FN = SQL scalar function
R = Rule
RF = Replication filter procedure
TR = SQL trigger
IF = SQL inline table-valued function
TF = SQL table-valued function
V = View
*/
USE AdventureWorks2008;
SELECT object_id (‘HumanResources.uspUpdateEmployeeHireInfo’)
GO
– 87671360
SELECT object_schema_name(87671360),object_name(87671360)
GO
– Returns object create statement
SELECT object_definition(87671360)
GO
——————————————————————–
How to find the first work day of a month?
Execute the following script in Query Editor to create a user-defined function (UDF) to calculate the first work day in a month:use AdventureWorks2008;
go
create function fnFirstWorkDayOfMonth( @Year int, @Month int)
returns datetime
as
begin
declare @FirstDayOfMonth datetime
set @FirstDayOfMonth = convert(datetime, convert(varchar,@Year)+‘-’+convert(varchar,@month)+‘-01′)
if datepart(weekday,@FirstDayOfMonth) not in (1,7)
return (@FirstDayOfMonth)
set @FirstDayOfMonth = dateadd(day,1,@FirstDayOfMonth)
if datepart(weekday,@FirstDayOfMonth) not in (1,7)
return (@FirstDayOfMonth)
return (dateadd(day,1,@FirstDayOfMonth))
end
go
select dbo.fnFirstWorkDayofMonth (
year(getdate()),
month(getdate())
)
go
———————————————————————————————–
How to use the timestamp datatype for version-stamping?
Execute the following script in Query Editor to demonstrate the usage of timestamp column for version-stamping. If any column in a table row is updated the timestamp (rowversion) will change for that row. Use tempdb;
– drop table NameAddress
Create table NameAddress
(
NameAddressID int identity primary key,
FirstName varchar(25),
LastName varchar(30),
timestamp
)
go
Insert NameAddress (FirstName, LastName)
values
(‘Tom’, ‘Jones’),
(‘Elizabeth’, ‘Taylor’)
go
Select * from NameAddress
go
/* Results
NameAddressID FirstName LastName timestamp
1 Tom Jones 0x000000000001155D
2 Elizabeth Taylor 0x000000000001155E
*/
Insert NameAddress (FirstName, LastName)
values
(‘Frank’, ‘Sinatra’),
(‘Tania’, ‘Zaetta’)
go
Select * from NameAddress
go
/* Result
NameAddressID FirstName LastName timestamp
1 Tom Jones 0x000000000001155D
2 Elizabeth Taylor 0x000000000001155E
3 Frank Sinatra 0x000000000001155F
4 Tania Zaetta 0×0000000000011560
*/
Update NameAddress Set FirstName=‘Elvis’, LastName=‘Presley’
where NameAddressID=1
go
Select * from NameAddress
go
/* Results
NOTE: timestamp changed on record 1
NameAddressID FirstName LastName timestamp
1 Elvis Presley 0×0000000000011561
2 Elizabeth Taylor 0x000000000001155E
3 Frank Sinatra 0x000000000001155F
4 Tania Zaetta 0×0000000000011560
*/
—————————–
How to create a table-valued function for object dependencies?
Execute the following script in Query Editor to create a table-valued functions for obtaining a list of dependent objects: USE AdventureWorks2008;
GO
– DROP FUNCTION fnDependentObjectList
CREATE FUNCTION fnDependentObjectList ( @Objectname sysname)
RETURNS @buffer table (Given sysname, [Dependent] sysname)
AS
BEGIN;
WITH cteDependence (DependentObjectID, DependentObjectName, GivenObjectName, GivenObjectID)
AS
(
SELECT DISTINCT
d.object_id,
OBJECT_NAME(d.object_id),
GivenObject = OBJECT_NAME(d.referenced_major_id),
GivenObjectID = d.referenced_major_id
FROM
sys.sql_dependencies d
JOIN sys.objects so
ON d.referenced_major_id = so.object_id
WHERE
d.referenced_major_id = object_id(@ObjectName)
UNION ALL
SELECT
d.object_id,
OBJECT_NAME(d.object_id),
OBJECT_NAME(referenced_major_id),
object_id
FROM
sys.sql_dependencies d
JOIN cteDependence dep
ON d.referenced_major_id = dep.DependentObjectID
WHERE
d.referenced_major_id <> d.object_id
)
INSERT @buffer
SELECT DISTINCT
GivenObjectName, DependentObjectName
FROM
cteDependence d
RETURN
END
GO
SELECT * FROM fnDependentObjectList (‘Production.Product’)
GO
———————————————–
How to obtain extended properties for table columns?
Execute the following script in Query Editor to get extended properties for the columns of the Product table:USE AdventureWorks2008;
GO
SELECT
ObjecType = objtype,
ObjectName = objname,
Name = name,
Value = value
FROM fn_listextendedproperty (NULL, ‘schema’, ‘Production’,
‘table’, ‘Product’, ‘column’, default);
GO
—————————————————————-
How to create schedule table for NASCAR races?
Execute the following script in Query Editor to create the Nascar table, populate it and run a SELECT query:USE AdventureWorks2008
GO
– drop table [dbo].[Nascar]
CREATE TABLE [dbo].[Nascar](
[NascarID] [int] identity(1,1) primary key,
[Date] [date] NULL,
[Race] [varchar](55) NULL,
[Venue] [varchar](75) NULL,
[TV/Start] [varchar](35) NULL,
[Radio] [varchar](25) NULL,
[ModifiedDate] [DateTime] default (getdate())
) ON [PRIMARY]
GO
INSERT [dbo].[Nascar]([Date], Race, Venue, [TV/Start], Radio)
VALUES
(’2008-02-09′,‘Budweiser Shootout’,‘Daytona International Speedway’,‘FOX/8 p.m.’,‘MRN/8:30 p.m.’)
,(’2008-02-14′,‘Gatorade Duel 1′,‘Daytona International Speedway’,‘SPEED/2 p.m.’,‘MRN/2 p.m.’)
,(’2008-02-14′,‘Gatorade Duel 2′,‘Daytona International Speedway’,‘SPEED/3:30 p.m.’,‘MRN/3:30 p.m.’)
,(’2008-02-17′,‘Daytona 500′,‘Daytona International Speedway’,‘FOX/2 p.m.’,‘MRN/3:20 p.m.’)
,(’2008-02-25′,‘Auto Club 500′,‘Auto Club Speedway’,‘FOX/1 p.m.’,‘MRN/1 p.m.’)
,(’2008-03-02′,‘UAW-Dodge 400′,‘Las Vegas Motor Speedway’,‘FOX/3:30 p.m.’,‘PRN/4:30 p.m.’)
,(’2008-03-09′,‘Kobalt Tools 500′,‘Atlanta Motor Speedway’,‘FOX/1:30 p.m.’,‘PRN/2 p.m.’)
,(’2008-03-16′,‘Food City 500′,‘Bristol Motor Speedway’,‘FOX/1:30 p.m.’,‘PRN/2 p.m.’)
,(’2008-03-30′,‘Goody”s Cool Orange 500′,‘Martinsville Speedway’,‘FOX/1:30 p.m.’,‘MRN/2 p.m.’)
,(’2008-04-06′,‘Samsung 500′,‘Texas Motor Speedway’,‘FOX/1:30 p.m.’,‘PRN/2 p.m.’)
,(’2008-04-12′,‘Subway Fresh Fit 500′,‘Phoenix International Raceway’,‘FOX/8 p.m.’,‘MRN/8:30 p.m.’)
,(’2008-04-27′,‘Aaron”s 499′,‘Talladega Superspeedway’,‘FOX/1 p.m.’,‘MRN/2 p.m.’)
,(’2008-05-03′,‘Crown Royal Presents the Dan Lowry 400′,‘Richmond International Raceway’,‘FOX/7 p.m.’,‘MRN/7:30 p.m.’)
,(’2008-05-10′,‘Dodge Challenger 500′,‘Darlington Raceway’,‘FOX/7 p.m.’,‘MRN/7:20 p.m.’)
,(’2008-05-17′,‘NASCAR Sprint All-Star Race’,‘Lowe”s Motor Speedway’,‘SPEED/7 p.m.’,‘MRN/7:30 p.m.’)
,(’2008-05-25′,‘Coca-Cola 600′,‘Lowe”s Motor Speedway’,‘FOX/5 p.m.’,‘PRN/5:30 p.m.’)
,(’2008-06-01′,‘Best Buy 400 Benefiting Student Clubs for Autism Speaks’,‘Dover International Speedway’,‘FOX/1:30 p.m.’,‘MRN/2 p.m.’)
,(’2008-06-08′,‘Pocono 500′,‘Pocono Raceway’,‘TNT/12:30 p.m.’,‘MRN/2 p.m.’)
,(’2008-06-15′,‘LifeLock 400′,‘Michigan International Speedway’,‘TNT/12:30 p.m.’,‘MRN/2 p.m.’)
,(’2008-06-22′,‘Toyota/Save Mart 350′,‘Infineon Raceway’,‘TNT/3:30 p.m.’,‘PRN/5 p.m.’)
,(’2008-06-29′,‘LENOX Industrial Tools 301′,‘New Hampshire Motor Speedway’,‘TNT/12:30 p.m.’,‘MRN/2 p.m.’)
,(’2008-07-05′,‘Coke Zero 400 Powered By Coca-Cola’,‘Daytona International Speedway’,‘TNT/6:30 p.m.’,‘MRN/8 p.m.’)
,(’2008-07-12′,‘LifeLock.com 400′,‘Chicagoland Speedway’,‘TNT/6:30 p.m.’,‘MRN/8 p.m.’)
,(’2008-07-27′,‘Allstate 400 at The Brickyard’,‘Indianapolis Motor Speedway’,‘ESPN/1 p.m.’,‘IMS/2 p.m.’)
,(’2008-08-03′,‘Pennsylvania 500′,‘Pocono Raceway’,‘ESPN/1 p.m.’,‘MRN/2 p.m.’)
,(’2008-08-10′,‘Centurion Boats at The Glen’,‘Watkins Glen International’,‘ESPN/1 p.m.’,‘MRN/2 p.m.’)
,(’2008-08-17′,’3M Performance 400′,‘Michigan International Speedway’,‘ESPN/1 p.m.’,‘MRN/2 p.m.’)
,(’2008-08-23′,‘Sharpie 500′,‘Bristol Motor Speedway’,‘ESPN/7 p.m.’,‘PRN/8 p.m.’)
,(’2008-08-31′,‘Pepsi 500′,‘Auto Club Speedway’,‘ESPN/7 p.m.’,‘MRN/8 p.m.’)
,(’2008-09-06′,‘Chevy Rock & Roll 400′,‘Richmond International Raceway’,‘ABC/7 p.m.’,‘MRN/7:30 p.m.’)
,(’2008-09-14′,‘Sylvania 300′,‘New Hampshire Motor Speedway’,‘ABC/1 p.m.’,‘MRN/2 p.m.’)
,(’2008-09-21′,‘Dover 400′,‘Dover International Speedway’,‘ABC/1 p.m.’,‘MRN/2 p.m.’)
,(’2008-09-28′,‘Kansas 400′,‘Kansas Speedway’,‘ABC/1 p.m.’,‘MRN/2 p.m.’)
,(’2008-10-05′,‘Amp Energy 500′,‘Talladega Superspeedway’,‘ABC/1 p.m.’,‘MRN/2 p.m.’)
,(’2008-10-11′,‘Bank of America 500′,‘Lowe”s Motor Speedway’,‘ABC/7 p.m.’,‘PRN/7:25 p.m.’)
,(’2008-10-19′,‘NASCAR Sprint Cup Series 500′,‘Martinsville Speedway’,‘ABC/1 p.m.’,‘MRN/1:30 p.m.’)
,(’2008-10-26′,‘Pep Boys Auto 500′,‘Atlanta Motor Speedway’,‘ABC/1 p.m.’,‘PRN/2 p.m.’)
,(’2008-11-02′,‘Dickies 500′,‘Texas Motor Speedway’,‘ABC/3 p.m.’,‘PRN/3:30 p.m.’)
,(’2008-11-09′,‘Checker Auto Parts 500 presented by Pennzoil’,‘Phoenix International Raceway’,‘ABC/3 p.m.’,‘MRN/3:45 p.m.’)
,(’2008-11-16′,‘Ford 400′,‘Homestead-Miami Speedway’,‘ABC/3 p.m.’,‘MRN/3:45 p.m.’)
GO
SELECT * FROM [dbo].[Nascar] ORDER BY [Date]
GO
——————————————————————-
No comments:
Post a Comment