Copy and Paste

Quick page of some common queries using object and data management views.

Use the copy command in the upper right of each code block.

Note to self, use the Enlighter Source Code block.

---

Table / Column Names

--sys.schemas
--sys.tables
SELECT  @@SERVERNAME as ServerName,
        s.name AS SchemaName,
        t.name AS TableName
FROM    sys.schemas s LEFT OUTER JOIN
        sys.tables t ON s.schema_id = t.schema_id
WHERE   1=1
ORDER BY 1,2,3;

--sys.schemas
--sys.tables
--sys.columns
--sys.types
SELECT  @@SERVERNAME as ServerName,
        s.name AS SchemaName, 
        t.name AS TableName, 
        c.name AS ColumnName, 
        ty.name as DataType,
        CONCAT('SELECT * FROM ',s.name,'.',t.name,';') as QuickQuery
FROM    sys.schemas s LEFT OUTER JOIN
        sys.tables t ON s.schema_id = t.schema_id INNER JOIN
        sys.columns c ON t.object_id = c.object_id INNER JOIN
        sys.types ty ON ty.user_type_id = c.user_type_id
WHERE   1=1
ORDER BY 1,2,3,4;

Missing Indexes

--sys.dm_db_missing_index_groups
--sys.dm_db_missing_index_group_stats
--sys.dm_db_missing_index_details
SELECT
        GETDATE() AS runtime
        ,@@Servername
        ,REPLACE(REPLACE(mid.statement,']',''),'[','') AS tablename
        ,migs.avg_total_user_cost
        ,migs.avg_user_impact
        ,migs.user_seeks
        ,migs.user_scans
        
        --Pulled from the internet.
        ,CONVERT (DECIMAL (28,1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) AS improvement_measure
        ----------------------------
        --Index creation syntax
        ,'CREATE INDEX missing_index_' + 'INDEX_NAME ON ' + mid.statement + ' (' + ISNULL (mid.equality_columns,'') + 
                    CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL
                         THEN ','
                         ELSE ''
                         END + ISNULL (mid.inequality_columns, '') + ')' + ISNULL (' INCLUDE (' + mid.included_columns + ')', '')
        AS create_index_statement
        ----------------------------
        ,mid.database_id
        ,mid.[object_id]
FROM    sys.dm_db_missing_index_groups AS mig INNER JOIN
        sys.dm_db_missing_index_group_stats AS migs ON migs.group_handle = mig.index_group_handle INNER JOIN
        sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle
WHERE    1=1
         AND mid.statement NOT LIKE '%msdb%'
ORDER BY
        user_seeks desc,
        migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC;

--EXEC sp_helptext 'schecma.name'

---

Index Statistics: Determine Fragmentation

--sys.dm_db_index_physical_stats
SELECT  @@servername AS ServerName,
        s.[name] AS 'Schema',
        t.[name] AS 'Table',
        i.[name] AS 'Index',
        idxstats.avg_fragmentation_in_percent,
        idxstats.page_count
FROM    sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) idxstats INNER JOIN
        sys.tables t ON t.object_id = idxstats.object_id INNER JOIN
        sys.schemas s ON t.schema_id = s.schema_id INNER JOIN
        sys.indexes i ON i.object_id = idxstats.object_id AND idxstats.index_id = i.index_id
WHERE   1=1 
        AND idxstats.database_id = DB_ID()
        AND i.[name] IS NOT NULL
        AND idxstats.avg_fragmentation_in_percent > 0
ORDER BY
        idxstats.avg_fragmentation_in_percent DESC;

---

SQL Server Agent

--dbo.sysjobsstep - Contains the information for each step in a job to be executed by SQL Server Agent. This table is stored in the msdb database.
--dbo.sysjobs - Stores the information for each scheduled job to be executed by SQL Server Agent. This table is stored in the msdb database
SELECT  @@SERVERNAME AS ServerName,
        s.step_id AS 'StepID',
        j.[name] AS 'SQLAgentJobName',
        s.[database_name] AS 'DBName',
        s.command AS 'Command'
FROM    msdb.dbo.sysjobsteps s INNER JOIN 
        msdb.dbo.sysjobs AS j ON s.job_id = j.job_id
WHERE   1=1 
        AND s.command LIKE '%example_command%';

---

Cursor

DECLARE @vMyVariable VARCHAR(100);
DECLARE test_cursor CURSOR FOR (SELECT 'Hello World' UNION SELECT 'Goodbye World');

OPEN test_cursor;
FETCH NEXT FROM test_cursor INTO @vMyVariable;
	WHILE @@FETCH_STATUS = 0
		BEGIN
		PRINT(CONCAT('The value is ', @vMyVariable));
		FETCH NEXT FROM test_cursor INTO @vMyVariable;
		END
CLOSE test_cursor;
DEALLOCATE test_cursor;

---

sys.sql_modules: Returns the definition of a stored procedure.

sp_helptext ‘MyStoredProcedure’ can also be used

SELECT  o.name, o.type_desc, o.create_date, o.modify_date, m.*
FROM    sys.sql_modules m LEFT OUTER JOIN
        sys.all_objects o on m.object_id = o.object_id
WHERE   m.definition like  '%myText%';

ASCII Function: Returns the ASCII values of a string

DECLARE @vMyString VARCHAR(100) = 'Hello World';
DECLARE @vLength INTEGER = LEN(@vMyString);
DECLARE @vPosition INTEGER = 0;
DECLARE @vAscii VARCHAR(MAX) = '';

WHILE @vLength >= @vPosition
BEGIN
  SELECT @vAscii = @vAscii + CONCAT(ASCII(SUBSTRING(@vMyString,@vPosition,1)),',');
  SELECT @vPosition = @vPosition + 1;
END;

SET @vAscii = SUBSTRING(@vAscii,2,LEN(@vAscii)-2);--Removes beginning and ending commas
SELECT @vAscii AS AsciiCodes;

Random Numbers

Use the function ABS(CHECKSUM(NEWID()) % 10) to generate a random number between 1 and 10. The following loop generates 10,000 random numbers between 1 and 10 and then provides proof that each number has an equal chance of being selected.

DROP TABLE IF EXISTS #RandomNumbers;
GO
CREATE TABLE #RandomNumbers
(
MyRandomNumber INTEGER NOT NULL
);
GO

DECLARE @i INTEGER = 1;
DECLARE @loops INTEGER = 100000;
WHILE @i <= @loops
BEGIN
    INSERT INTO #RandomNumbers SELECT ABS(CHECKSUM(NEWID()) % 10) + 1;
    SET @i = @i + 1;
END
GO
----------------------------------------------------------------
----------------------------------------------------------------
WITH cte_Total AS
(
SELECT COUNT(MyRandomNumber) AS MyTotal FROM #RandomNumbers
),
cte_Count AS
(
SELECT  MyRandomNumber, COUNT(*) AS MyCount
FROM    #RandomNumbers a CROSS JOIN
        cte_Total b
GROUP BY MyRandomNumber
)
SELECT  MyRandomNumber, 
        MyCount, 
        MyCount / CAST(MyTotal AS NUMERIC(10,2))
FROM    cte_Count a CROSS JOIN
        cte_Total b;
GO

sp_helptext

/*
--Create an example stored procedure
CREATE OR ALTER PROCEDURE spTest AS
BEGIN
SELECT 'Hello World'
END;
GO
*/

DECLARE @vTableVariable TABLE(SpText VARCHAR(MAX));
DECLARE @vStoredProcedureName VARCHAR(255) = 'spTest';

INSERT INTO @vTableVariable
EXEC sp_helptext @vStoredProcedureName;

SELECT * FROM @vTableVariable;

/*
--If you want to save to a temporary table
INSERT INTO #spTest
SELECT	@vStoredProcedureName,
		ROW_NUMBER() OVER(ORDER BY GETDATE()),
		SpText
FROM	@vTableVariable;
*/

Data Profiling

SET NOCOUNT ON 

DROP TABLE IF EXISTS #MyTempTable;
GO

-----------------------------------------------------------------
-----------------------------------------------------------------

DECLARE @vSchemaName NVARCHAR(100) = 'dbo';
DECLARE @vTableName NVARCHAR(100) = 'MyTable';
DECLARE @vSQLStatement NVARCHAR(1000) = 'UPDATE #MyTempTable SET RecordCount = (SELECT COUNT([ColumnName]) FROM SchemaName.TableName) WHERE RowNumber = vRowNumber';
SET @vSQLStatement = REPLACE(@vSQLStatement,'SchemaName',@vSchemaName);
SET @vSQLStatement = REPLACE(@vSQLStatement,'TableName',@vTableName);

SELECT  ROW_NUMBER() OVER (ORDER BY t.[Name], c.[Name]) AS RowNumber,
        @@SERVERNAME AS ServerName,
        s.[Name] AS SchemaName, 
        t.[Name] AS TableName, 
        c.[Name] AS ColumnName, 
        ty.[Name] AS DataType,
        REPLACE(@vSQLStatement,'ColumnName',c.[Name]) AS SQLStatement,
        CAST(NULL AS BIGINT) AS RecordCount
INTO    #MyTempTable
FROM    sys.Schemas s LEFT OUTER JOIN
        sys.Tables t ON s.Schema_id = t.Schema_id INNER JOIN
        sys.Columns c ON t.Object_id = c.Object_id INNER JOIN
        sys.Types ty ON ty.User_Type_ID = c.User_Type_ID
WHERE   1=1 AND 
        s.[Name] = @vSchemaName AND 
        t.[Name] = @vTableName
        --AND ty.Type IN ('ntext','nvarchar','text','varchar)
ORDER BY 1,2,3,4,5;
GO

-----------------------------------------------------------------
-----------------------------------------------------------------

DECLARE @vRowNumber INTEGER;
DECLARE @vSQLStatement VARCHAR(8000);

DECLARE mycursor CURSOR FOR (SELECT RowNumber, SQLStatement FROM #MyTempTable);
OPEN mycursor;

FETCH NEXT FROM mycursor INTO @vRowNumber, @vSQLStatement;
    WHILE @@FETCH_STATUS = 0
        BEGIN
        SET @vSQLStatement = REPLACE(@vSQLStatement,'vRowNumber',@vRowNumber)
        PRINT(CONCAT('The value is ', @vSQLStatement));
        EXEC (@vSQLStatement);
        FETCH NEXT FROM mycursor INTO @vRowNumber, @vSQLStatement;
        END
CLOSE mycursor;
DEALLOCATE mycursor;

SELECT * FROM #MyTempTable ORDER BY 1;

Write a Databricks Hive table as a csv to a DataLake

# Reading the test_view into a data frame and displaying the contents.

df=sqlContext.sql("select * from schema.TableName")
#type(df)

target_folder_path='abfss://source@'+datalakename+'.dfs.core.windows.net/folderpath'
print(target_folder_path)

#df.write.option("header", "false").mode("overwrite").save(target_folder_path)
df.write.option('header',True).csv(target_folder_path)

Drop a database

USE MASTER;
GO

ALTER DATABASE <name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

DROP DATABASE <name>;