tcartwright

Converted into a module: https://github.com/tcartwright/tcdbtools

PS Gallery link: https://www.powershellgallery.com/packages/tcdbtools/

tcartwright

@rem bat file to ease use of the split script

@%~d0
@cd "%~dp0"

powershell.exe -ExecutionPolicy Bypass -NoLogo -NoProfile -file "%~dp0Generate Security.ps1" -OutPutFile "%~dp0Security.sql"

@echo Done.
@pause

tcartwright

@echo OFF
@setlocal ENABLEEXTENSIONS

@REM Author: Tim Cartwright

reg query "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\NET Framework Setup\NDP"

@rem https://msdn.microsoft.com/en-us/library/hh925568%28v=vs.110%29.aspx?f=255&MSPPError=-2147217396#net_d

@echo.

tcartwright

@echo on
@setlocal EnableDelayedExpansion

@set ps=%windir%\system32\WindowsPowerShell\v1.0\powershell.exe -ExecutionPolicy Bypass -NoLogo -NonInteractive -NoProfile
%ps% -File "%~dp0Get Local DB Info.ps1"

@echo.
@pause

tcartwright

@rem resources
@rem http://www.brentozar.com/archive/2008/09/finding-your-san-bottlenecks-with-sqlio/
@rem http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=20163

@rem START MODIFICATION AREA START 
@rem change the drive letter to match the desired drive to test
@set testfile=c:\testfile.dat
@rem change the number to the number of gb's to test with, this should be roughly the same size as your db
@set size=20
@rem the number of threads to use for the test

tcartwright

DECLARE @Sql VARCHAR(MAX) = (
	SELECT 'DBCC FREEPROCCACHE (0x' + CONVERT( VARCHAR(MAX), [ecp].[plan_handle], 2) + ') WITH NO_INFOMSGS; ' + CHAR(10)
	FROM   [sys].[dm_exec_cached_plans] [ecp]
	WHERE  [ecp].[usecounts] = 1
			AND [ecp].[objtype] IN('Adhoc', 'Prepared')
	FOR XML PATH('')
);

SELECT @sql
--EXEC (@sql)

tcartwright

Converted into a module: https://github.com/tcartwright/tcdbtools

PS Gallery link: https://www.powershellgallery.com/packages/tcdbtools/

tcartwright

IF OBJECT_ID('tempdb..#CrossDbRefs') IS NOT NULL BEGIN
	DROP TABLE #CrossDbRefs
END

CREATE TABLE [#CrossDbRefs](
	[referencing_database_name] SYSNAME NOT NULL,
	[referencing_object]        SYSNAME NULL,
	[referencing_object_type]   VARCHAR(20) NULL,
	[referenced_database_name]  SYSNAME NULL,
	[referenced_entity_name]    SYSNAME NULL

tcartwright

IF OBJECT_ID('tempdb..#tbl') IS NOT NULL DROP TABLE #tbl

CREATE TABLE #tbl (
	[DBName] sysname,
	[TableName] sysname,
	[ColumnName] sysname,
	[DataType] sysname
)

EXEC master.dbo.sp_MSforeachdb @command1 = N'

tcartwright

DECLARE @objects TABLE (RecID INT IDENTITY(1,1), name nvarchar(512), type varchar(5))
DECLARE @null_data TABLE (null_data varchar(1))
-- retrieve the list of objects
INSERT INTO @objects(name, type)
	SELECT 
		'[' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + ']', o.type 
	FROM sys.objects o
	WHERE o.is_ms_shipped = 0
		AND (o.type <> 'V' OR OBJECTPROPERTY(o.object_id, 'IsSchemaBound') = 0)
		AND (o.type in ('P', 'V')