This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
--- Read this first: https://sqlsunday.com/2016/06/16/copying-data-with-foreign-keys-and-identity-columns/ | |
IF (OBJECT_ID('dbo.LoadTableBlobs') IS NULL) EXEC('CREATE PROCEDURE dbo.LoadTableBlobs AS --') | |
GO | |
/* | |
Copyright Daniel Hutmacher under Creative Commons 4.0 license with attribution. | |
http://creativecommons.org/licenses/by/4.0/ | |
Source: http://sqlsunday.com/downloads/ |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- More information: https://sqlsunday.com/2013/03/24/decrypting-sql-objects/ | |
SET NOCOUNT ON | |
DECLARE @owner sysname='dbo', @name sysname='sp_someprocedure'; | |
----------------------------------------------------------- | |
--- Declarations: | |
DECLARE @offset int=1; | |
DECLARE @datalength int; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
------------------------------------------------------------------------------- | |
--- | |
--- Reverse $PARTITION function - returns the boundary values for a given | |
--- partition function and partition number. | |
--- | |
--- Returns: | |
--- | |
--- Lower_Boundary sql_variant NULL for first partition | |
--- Lower_Boundary_Condition varchar(2) "<=" or "<" |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# https://stackoverflow.com/a/53034595/5471286 | |
function ConvertTo-UTF8 { | |
param( | |
[Parameter(ValueFromPipeline)] [string]$ISOString | |
) | |
return ([Text.Encoding]::UTF8.GetString( ` | |
[Text.Encoding]::GetEncoding(28591).GetBytes($ISOString))) | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
Find the widest tables by bytes/row | |
*/ | |
SELECT OBJECT_SCHEMA_NAME(ps.[object_id])+N'.'+OBJECT_NAME(ps.[object_id]) AS [Object], | |
ix.[name] AS [Index], | |
ps.partition_number AS [Partition], | |
p.data_compression_desc AS [Compression], | |
REPLACE(CONVERT(varchar(20), CAST(SUM(ps.row_count) AS money), 1), '.00', '') AS [Row count], | |
REPLACE(CONVERT(varchar(20), FLOOR(CAST(SUM(8192.*ps.in_row_used_page_count) /NULLIF(SUM(ps.row_count), 0) AS money)), 1), '.00', '') AS [In-row, bytes/row], |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# Export your WordPress blog to an XML file. Then: | |
$xmlFile = "./export/sqlsundaycom.wordpress.2021-05-23.001.xml" | |
$matches = Select-String -Path $xmlFile -Pattern '(?<=src\=\")http(.*?)(?=\")' -AllMatches ` | |
| % { $_.Matches } | |
| % { $_.Value } | |
| Sort-Object -Unique | |
foreach ($url in $matches) { | |
#$url |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Function Invoke-ParamQuery { | |
param( | |
[String]$Query, | |
$Parameters=@{}, | |
[Data.SqlClient.SqlConnection]$Conn, | |
[int]$Timeout=3, | |
[switch]$CloseConn, | |
[switch]$DiscardResults | |
) | |
if ($conn.State -eq "Closed") { |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT j.[name] AS Job, | |
s.step_id AS Step, | |
s.step_name AS [Step name], | |
s.subsystem AS [Subsystem], | |
s.[command], | |
x2.p AS [SSIS path], | |
env.environment_name AS [Environment name] | |
FROM msdb.dbo.sysjobs AS j | |
INNER JOIN msdb.dbo.sysjobsteps AS s ON j.job_id=s.job_id | |
OUTER APPLY ( |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
IF (SCHEMA_ID('Deprecated') IS NULL) | |
EXEC('CREATE SCHEMA [Deprecated];'); | |
SELECT 'ALTER SCHEMA [Deprecated] TRANSFER '+QUOTENAME(OBJECT_SCHEMA_NAME([object_id]))+'.'+QUOTENAME(OBJECT_NAME([object_id]))+';' | |
FROM sys.objects | |
WHERE [schema_id] NOT IN (SCHEMA_ID('Deprecated'), SCHEMA_ID('sys')) | |
AND [type] NOT IN ('PK', 'F', 'D', 'UQ') | |
AND ([name] LIKE '%xx%' OR [name] LIKE '%[0-9][0-9][0-9][0-9][0-9][0-9]%' | |
OR [name] LIKE '%[ _]temp' OR [name] LIKE 'temp[ _]%' | |
OR [name] LIKE '%[ _]old' OR [name] LIKE 'old[ _]%' |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- TRANSLATE() and STRING_SPLIT() work with SQL Server 2016+, STRING_AGG() with 2017+. | |
-- language_id=1033 is English (simplified) | |
SELECT STRING_AGG(REPLACE(TRANSLATE(s.[value] COLLATE database_default, '():.,-/='';', '**********'), '*', ''), ' ') | |
FROM sys.messages AS msg | |
CROSS APPLY STRING_SPLIT(msg.[text], ' ') AS s | |
WHERE msg.language_id=1033 | |
--AND s.[value] COLLATE database_default NOT IN ('the', 'is', 'to', 'not', 'a', 'for', 'in', 'be', 'of', 'or', 'cannot', 'and') | |
AND s.[value] COLLATE database_default NOT LIKE '%[%@=]%' | |
GROUP BY msg.message_id |
OlderNewer