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 o.[type], | |
ISNULL(NULLIF(t.temporal_type_desc, 'NON_TEMPORAL_TABLE'), o.[type_desc]) AS object_type, | |
s.[name]+'.'+o.[name] AS [object_name], | |
(CASE WHEN i.is_unique=1 THEN 'UNIQUE ' ELSE '' END)+i.[type_desc] AS index_type, | |
(CASE WHEN kc.is_system_named=1 THEN '' ELSE i.[name] END) AS index_name, | |
ISNULL('WHERE '+i.filter_definition, '') AS index_filter, | |
ISNULL(' ON '+(CASE WHEN ds.is_default=0 THEN ds.[name] END)+ISNULL('('+c.[name]+')', ''), '') AS data_space, | |
ISNULL(CAST(NULLIF(NULLIF(i.fill_factor, 0), 100) AS varchar(10))+'%', '') AS fill_factor, | |
ISNULL(CAST(p.partition_number AS varchar(10))+'/'+CAST(NULLIF(MAX(p.partition_number) OVER (PARTITION BY p.[object_id], p.index_id), 1) AS varchar(10)), '') AS [partition], | |
ISNULL(NULLIF(p.data_compression_desc, 'NONE'), '') AS [compression], |
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
DECLARE @object_id int=OBJECT_ID('schema_name.table_name'); | |
DECLARE @name nvarchar(max)=(SELECT QUOTENAME(SCHEMA_NAME([schema_id]))+'.'+QUOTENAME([name]) FROM sys.objects WHERE [object_id]=@object_id), | |
@cols nvarchar(max)=N'', | |
@query nvarchar(max)=N'', | |
@pagesize varchar(10)=N'100'; | |
WITH cte AS ( | |
SELECT c.column_id, c.[name], t.[name] AS [type] |
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 |
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
DECLARE @object_id int=OBJECT_ID('dbo.tablename'), | |
@rowcount bigint=10000000; | |
SELECT N'UPDATE STATISTICS '+ | |
--- Name of the table | |
QUOTENAME(OBJECT_SCHEMA_NAME(@object_id))+N'.'+QUOTENAME(OBJECT_NAME(@object_id))+ |
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
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
# 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
/* | |
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
# 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))) | |
} |
OlderNewer