Skip to content

Instantly share code, notes, and snippets.

View sheldonhull's full-sized avatar
👋
Hi! Who keeps their github status up to date? You get a 🌮 just for reading this

sheldonhull sheldonhull

👋
Hi! Who keeps their github status up to date? You get a 🌮 just for reading this
115 followers · 318 following
View GitHub Profile
@sheldonhull
sheldonhull / RestoreOverwrite.sql
Last active August 29, 2015 14:26
successfully restored db back to the starting point
begin try
print 'restore database [PoorDB]'
restore database [PoorDB]
from disk = N'E:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\PoorDB.bak'
with replace, recovery;
print 'alter database PoorDB set online with rollback immediate;'
alter database PoorDB set online with rollback immediate;
@sheldonhull
sheldonhull / KillAllConnectionsOnDb.sql
Created July 28, 2015 18:20
Kill all user connections on a db - originally written by Matthew Haugen
-- originally written in help on Stack Overflow
-- Credit to Matthew Haugen http://goo.gl/0SJYiq
USE master
GO
DECLARE @kill varchar(8000) = '';
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), spid) + ';'
FROM master..sysprocesses
WHERE dbid = db_id('MyDB')
@sheldonhull
sheldonhull / SetOfflineAndMultiUser.sql
Created July 28, 2015 18:24
Basic actions to set to offline and multi-user
set deadlock_priority high
alter database PoorDB set offline with rollback immediate;
alter database PoorDB set multi_user with rollback immediate;
@sheldonhull
sheldonhull / CheckAllConstraintsInDb.sql
Last active August 29, 2015 14:26
check all the constraints in the database. You could adapt with only check, or only FK if you change the query UNION'd
/*******************************************************
Check all constraints in db, FK, and check.
Run through each one and log if error occurs
------------------------- history -------------------------
2015-08-06 sqlbarbarian @ 13:27:21; adapted from the original basic info provided by [brentozar spblitz info](http://www.brentozar.com/blitz/foreign-key-trusted/) Thanks Brent!
*******************************************************/
set noexec off; -- remove when ready to run
set nocount on;
@sheldonhull
sheldonhull / DateLocalization.sql
Created August 26, 2015 16:55
Date localization snippets, helpful to test a script that may have different string or date formats.
/*******************************************************
review your connection details to see your default language
*******************************************************/
select
LanguageId = @@langid
,LanguageName = @@language
/*******************************************************
Localization for *NEW* users created would use this
configuration option
@sheldonhull
sheldonhull / XMLAttribute.sql
Created September 14, 2015 15:00
Returning Results of XML as element vs attribute
/*******************************************************
Using column alias with quotes returns results as an attribute
*******************************************************/
select
main.TABLE_NAME as '@TableName'
from
INFORMATION_SCHEMA.COLUMNS main
where
(objectproperty(object_id(TABLE_NAME), 'IsView') = 1
or objectproperty(object_id(TABLE_NAME), 'IsTable') = 1)
@sheldonhull
sheldonhull / DropMirroredDb.sql
Last active October 14, 2015 21:30
Drop mirrored db's
use master;
select
case
when d.mirroring_state != 0 then 'print ''Turning mirroring off on ' + s.name + '''' + CHAR(10) + CHAR(13)
+ 'alter database ' + QUOTENAME(s.name) + ' set single-user with rollback immediate;' + CHAR(10) + CHAR(13)
+ 'alter database ' + QUOTENAME(s.name) + ' set partner off;' + CHAR(10) + CHAR(13)
else ''
end
+ 'GO' + CHAR(10) + CHAR(13)
+ 'print ''dropping ' + QUOTENAME(s.name) + '''' + CHAR(10) + CHAR(13)
@sheldonhull
sheldonhull / FKCascadeDeleteUpdatesList.sql
Created October 21, 2015 16:53
Evaluate the cascade deletes in database
if object_id('tempdb..#temp', 'U') is not null
begin
print 'Dropped #temp per existed'
drop table #temp;
end
select
sqid_sort = row_number() over (partition by t.object_id order by t.name)
,table_name = t.name
,fk_name = FK.name
@sheldonhull
sheldonhull / ConvertDataTableToString.cs
Created October 28, 2015 16:13
From http://stackoverflow.com/questions/1104121/how-to-convert-a-datatable-to-a-string-in-c
public static string ConvertDataTableToString(DataTable dataTable)
{
var output = new StringBuilder();
var columnsWidths = new int[dataTable.Columns.Count];
// Get column widths
foreach (DataRow row in dataTable.Rows)
{
for(int i = 0; i < dataTable.Columns.Count; i++)
@sheldonhull
sheldonhull / GetSpecificVersionOfFOlderFromTFS.cmd
Created October 30, 2015 15:49
From http://stackoverflow.com/questions/10432634/how-to-get-specific-version-of-folder-from-tfs-without-creating-a-workspace
tf workspace -new %JOB_NAME%;%user% -noprompt -server:http://%host%:8080/tfs/%project% -login:%user%,%password%
tf workfold -map $/Release/MilestoneX.X . -workspace:%JOB_NAME% -server:http://%host%:8080/tfs/%project% -login:%user%,%password%
tf get . -version:L%TFS_LABEL% -recursive -noprompt -login:%user%,%password%
tf workfold -unmap . -workspace:%JOB_NAME% -login:%user%,%password%
tf workspace -delete %JOB_NAME%;%user% -noprompt -server:http://%host%:8080/tfs/%project% -login:%user%,%password%