dhmacher

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],

dhmacher

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]

dhmacher

-- 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

dhmacher

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[ _]%'

dhmacher

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))+

dhmacher

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 (

dhmacher

Function Invoke-ParamQuery {
    param(
        [String]$Query,
        $Parameters=@{},
        [Data.SqlClient.SqlConnection]$Conn,
        [int]$Timeout=3,
        [switch]$CloseConn,
        [switch]$DiscardResults
    )
    if ($conn.State -eq "Closed") {

dhmacher

# 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

dhmacher

/*
    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],

dhmacher

# 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)))
}