Skip to content

Instantly share code, notes, and snippets.

View tcartwright's full-sized avatar

Tim Cartwright tcartwright

27 followers · 21 following
  • Houston, Texas
View GitHub Profile
@tcartwright
tcartwright / ScriptDatabase.md
Last active January 3, 2023 16:28
SQL SERVER: Powershell Script to script a database including its grants
@tcartwright
tcartwright / Generate Security.bat
Last active April 29, 2020 14:42
SQL SERVER: Generate Grants for a Database, and role memberships for a database. Can be run before a backup to store security and then applied post backup.
@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
tcartwright / Get Installed DotNet Frameworks.bat
Last active June 16, 2021 18:45
Get Installed DotNet Frameworks
@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
tcartwright / Get Local DB Info.bat
Created July 6, 2017 17:27
Get LocalDB Info
@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
tcartwright / Run SQLIO Tests.bat
Created July 6, 2017 17:28
Run SQLIO Tests
@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
tcartwright / ClearSingleUsePlansFromCache.sql
Created July 6, 2017 17:59
SQL SERVER: Clear single use plans from cache
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
tcartwright / FindColumnsWithDiffTypes.md
Last active January 13, 2023 00:18
SQL SERVER: Find columns with the same names but different types or sizes
@tcartwright
tcartwright / ListCrossDatabaseDependencies.sql
Last active February 12, 2021 19:36
SQL SERVER: List cross database dependencies
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
tcartwright / FindAllTablesWithColumnsAnsiPaddingOff.sql
Last active December 1, 2020 19:29
SQL SERVER: List tables with ANSI PADDING off
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
tcartwright / RefreshDBObjects.sql
Created July 6, 2017 18:13
SQL SERVER: Refresh all sql modules
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')