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