SomTripathi

--+---------------------------------------------------------------------------------------------------------------+--
--| Sync All Orphans in a Database
--+---------------------------------------------------------------------------------------------------------------+--

select 'sp_change_users_login ''auto_fix'',''' + name + '''' + char(13) + char(10) + 'GO' 
from sysusers 
where sid NOT IN 
	(select sid from master..syslogins ) 
	AND 
	islogin = 1 

SomTripathi

--+---------------------------------------------------------------------------------------------------------------+--
--| Sync an Orphan in all Databases
--+---------------------------------------------------------------------------------------------------------------+--

SET NOCOUNT ON 
exec sp_MSforeachdb 
'
use ? 
select db_name()
exec sp_change_users_login ''update_one'' , ''<login_name>'' , ''<login_name>'' 

SomTripathi

--+---------------------------------------------------------------------------------------------------------------+--
--| Get a Job-Hostory
--+---------------------------------------------------------------------------------------------------------------+--
--| Date	: 11:38 PM 9/10/2009
--| Purpose	: Get a job history. Place the Job-name and get results soon. In some cases when you have a huge
--| 		  historical info, SSMS-Job history fails due to time-out - this batch could help you. 
--| Compatible	: SQL Server 2000, SQL Server 2005
--+---------------------------------------------------------------------------------------------------------------+--
--| Author	: Som Dutt Tripathi
--+---------------------------------------------------------------------------------------------------------------+--

SomTripathi

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




CREATE PROCEDURE [dbo].[psp_table_size_report] 

SomTripathi

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




CREATE PROCEDURE [dbo].[psp_table_size_report] 

SomTripathi

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[psp_fragmentation_report] 
(
	@recipients varchar(300), 
	@subject varchar(800)='Fragmentation Report'

SomTripathi

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[psp_disk_size_report] 
(
	@threshold int=20, 
	@recipients varchar(300), 

SomTripathi

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[psp_db_size_report] 
(
	@threshold int=20, 
	@recipients varchar(300), 
	@subject varchar(800)='Database Size Report'

SomTripathi

DECLARE @HowManyHoursAhead int 

Set @HowManyHoursAhead = 2; -- <-- Change the hour here.

WITH OurJobs AS (
 SELECT job.job_id, job.[name]
 , CASE job.[description] WHEN 'No description available.' THEN NULL ELSE job.description END AS Description
 , job.date_modified
 , CASE sched.next_run_date
 WHEN 0 THEN 'Never'

SomTripathi

cls 

function setPrecision([Float]$number, [int]$precision ) 
{
	$prec="{0:N$precision}"
	$val=$($prec -f $number)
	
	return $val
}