If your Project server environment has enterprise calendars you can collection information about all the calendars from project server database.
The Query below collects information from MSP_Calendars, MSP_Calendars_Data, MSP_Projects tables of project server data and show information about each calendar separately.
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[INTECHCalenders]’) and OBJECTPROPERTY(id, N’IsUserTable’) = 1)
drop table [dbo].[INTECHCalenders]
print ‘table deleted’
GO
SELECT DISTINCT dbo.MSP_CALENDARS.CAL_NAME, dbo.MSP_CALENDARS.Cal_UId
INTO INTECHCalenders
FROM dbo.MSP_CALENDARS INNER JOIN
dbo.MSP_PROJECTS ON dbo.MSP_CALENDARS.PROJ_ID = dbo.MSP_PROJECTS.PROJ_ID CROSS JOIN
dbo.MSP_CALENDAR_DATA
WHERE (dbo.MSP_PROJECTS.PROJ_ID = 207)
Delete from INTECHCalenders Where CAl_name is NULL
DECLARE @Cal_UId varchar(10)
DECLARE CAL_Cursor CURSOR FOR
SELECT CAL_UID FROM INTECHCalenders
OPEN CAL_Cursor
FETCH FROM CAL_Cursor
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH CAL_Cursor INTO @Cal_UId
SELECT dbo.MSP_CALENDAR_DATA.CAL_UId, dbo.MSP_CALENDAR_DATA.CD_WORKING, dbo.MSP_CALENDAR_DATA.CD_FROM_DATE, dbo.MSP_CALENDAR_DATA.CD_TO_DATE,
dbo.MSP_CALENDAR_DATA.CD_TO_TIME1, dbo.MSP_CALENDAR_DATA.CD_FROM_TIME1, dbo.MSP_CALENDAR_DATA.CD_FROM_TIME2,
dbo.MSP_CALENDAR_DATA.CD_TO_TIME2, dbo.MSP_CALENDAR_DATA.CD_FROM_TIME3, dbo.MSP_CALENDAR_DATA.CD_TO_TIME3,
dbo.MSP_CALENDAR_DATA.CD_FROM_TIME4, dbo.MSP_CALENDAR_DATA.CD_TO_TIME4, dbo.MSP_CALENDAR_DATA.CD_FROM_TIME5,
dbo.MSP_CALENDAR_DATA.CD_TO_TIME5
FROM dbo.MSP_CALENDAR_DATA
WHERE dbo.MSP_CALENDAR_DATA.CAL_UID = @Cal_UId
ORDER BY CD_TO_TIME1 ASC
END
CLOSE CAL_Cursor
DEALLOCATE CAL_Cursor
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[INTECHCalenders]’) and OBJECTPROPERTY(id, N’IsUserTable’) = 1)
drop table [dbo].[INTECHCalenders]
print ‘table deleted’
GO