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.
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%';
Column Names
--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;
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)