4 Loop Web Development
  • Services
  • Information
  • Contact
  • Services
  • Information
  • Contact

SQL Server

How do I find a stored procedure containing keyword?

    SELECT  ROUTINE_NAME, ROUTINE_DEFINITION, LAST_ALTERED
    FROM INFORMATION_SCHEMA.ROUTINES 
    WHERE ROUTINE_DEFINITION LIKE '%keyword%' 
    AND ROUTINE_TYPE='PROCEDURE'
    ORDER BY LAST_ALTERED DESC
            

How do I loop through temp variable?

    DECLARE @tmpTable as TABLE (ID int, processed int)
    INSERT INTO @tmpTable
    SELECT ID, 0
    FROM table123

    DECLARE @tID int
    WHILE (SELECT COUNT(*) FROM @tmpTable WHERE processed = 0) > 0
    BEGIN
    
        SET @tID = 0
        
        SELECT TOP 1
            @tID = ID
        FROM @tmpTable
        WHERE processed = 0

        --do something with @tID
        
        
        UPDATE @tmpTable
        SET processed = 1
        WHERE ID = @tID					
    END	
            

How do I loop through temp variable #2?


    DECLARE @xmlZipCodes xml
    SET @xmlZipCodes = '<zips><zp cd="55306"/><zp cd="55543"/></'zips>


    --create temp table
    DECLARE @tmpZipTable as TABLE (idx smallint Primary Key IDENTITY(1,1), ZipCode varchar(20))

    --populate temp table with incoming xml data
    INSERT INTO @tmpZipTable
    SELECT e.value('@cd', 'varchar(20)') as ZipCode
    FROM @xmlZipCodes.nodes('//zp') tblCandidates(e)

    DECLARE @i int
    DECLARE @numrows int
    DECLARE @tmp_ZipCode varchar(100)

    --set variables	
    SET @i = 1
    SET @numrows = (SELECT COUNT(*) FROM @tmpZipTable)

    IF @numrows > 0
	    --enumerate the table
	    WHILE (@i <= (SELECT MAX(idx) FROM @tmpZipTable))
		    BEGIN
			    --get next candidate_email
			    SET @tmp_ZipCode = (SELECT ZipCode FROM @tmpZipTable WHERE idx = @i)
			
			    --do something with variable
			    print @tmp_ZipCode
					
			    --increment counter
			    SET @i = @i + 1
					
		    END
    
            

Create sql based on field found in table.

    --Create SQL to show tables with JobPosting_ID AND Client_ID
    SELECT 'SELECT * FROM ' + name + ' WHERE Client_ID = @Client_ID AND JobPosting_ID = @JobPosting_ID'
    FROM sys.tables 
    WHERE object_id IN ( SELECT id FROM syscolumns WHERE name = 'jobposting_id' )
    AND object_id in 
    ( SELECT id FROM syscolumns WHERE name = 'client_id' )
    ORDER BY name
            

Change the schema of a database object

    --TABLES
    SELECT 'ALTER SCHEMA dbo TRANSFER ' + TABLE_SCHEMA + '.' + TABLE_NAME + ';' 
    FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'oldschemaname'
            

Change the schema of a database object

    --Stored procedures, table, functions
    SELECT 'ALTER SCHEMA dbo TRANSFER ' + SysSchemas.Name + '.' + DbObjects.Name + ';'
    FROM sys.Objects DbObjects
    INNER JOIN sys.Schemas SysSchemas ON DbObjects.schema_id = SysSchemas.schema_id
    WHERE SysSchemas.Name = 'oldschemaname'
    AND (DbObjects.Type IN ('U', 'P', 'V', 'FN'))
            

See table schema name(s)

  SELECT s.name
  FROM sys.schemas AS s
  INNER JOIN sys.tables AS t
  ON s.[schema_id] = t.[schema_id]
  WHERE t.name = N'BidTool';

Change the schema of a table

ALTER SCHEMA dbo TRANSFER [db_owner].BidTool;

Stored procedure that is asp/aspx helper

-- =============================================
-- Author:	
-- Create date: 2016-04-05
-- Description:	ASP helper sp
-- USAGE: EXEC asp_Helper 'tablename'
-- =============================================
CREATE PROCEDURE [dbo].[asp_Helper] 
    -- Add the parameters for the stored procedure here
    @TABLE_NAME varchar(55)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    select 'sqlCmd.Parameters.Add(New SqlParameter("@' + COLUMN_NAME + '", SqlDbType.' + data_type + ', ' + cast(isnull(character_maximum_length, '') as varchar(10)) + ')).Value = ' + COLUMN_NAME
    from INFORMATION_SCHEMA.COLUMNS 
    where table_name = @TABLE_NAME
    order by ordinal_position

    select '@' + COLUMN_NAME + ' ' + case WHEN DATA_TYPE = 'varchar' THEN 'varchar(' + CASE WHEN CHARACTER_MAXIMUM_LENGTH = -1 THEN 'MAX' ELSE CAST(CHARACTER_MAXIMUM_LENGTH as varchar(25)) END + ')'
    WHEN DATA_TYPE = 'int' THEN 'int'
    WHEN DATA_TYPE = 'datetime' THEN 'datetime' END + ','
    from INFORMATION_SCHEMA.COLUMNS 
    where table_name = @TABLE_NAME
    order by ordinal_position
    
    select '@' + COLUMN_NAME + ',' 
    from INFORMATION_SCHEMA.COLUMNS 
    where table_name = @TABLE_NAME
    order by ordinal_position
    
    select 'da.SelectCommand.Parameters.Add("@' + COLUMN_NAME + '", SqlDbType.' + data_type + case WHEN isnull(character_maximum_length, 0) > 0 THEN ', ' + cast(isnull(character_maximum_length, 0) as varchar(10)) ELSE '' END + ').Value = ' + COLUMN_NAME
    from INFORMATION_SCHEMA.COLUMNS 
    where table_name = @TABLE_NAME
    order by ordinal_position
    
    select case WHEN IS_NULLABLE = 'YES' THEN 'Optional ' ELSE '' END + 'ByVal ' + COLUMN_NAME + ' ' + case WHEN DATA_TYPE = 'varchar' THEN ' As String ' + case WHEN IS_NULLABLE = 'YES' THEN '= ""' ELSE '' END + ', _'
    WHEN DATA_TYPE = 'int' THEN ' As Integer ' + case WHEN IS_NULLABLE = 'YES' THEN '= 0' ELSE '' END + ', _'
    WHEN DATA_TYPE = 'datetime' THEN 'As String' + case WHEN IS_NULLABLE = 'YES' THEN '= ""' ELSE '' END + ', _' END
    from INFORMATION_SCHEMA.COLUMNS 
    where table_name = @TABLE_NAME
    order by ordinal_position
END
            

Missing Index Script

    -- Original Author: Pinal Dave (C) 2011
    SELECT TOP 25
        dm_mid.database_id AS DatabaseID,
        dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,
        dm_migs.last_user_seek AS Last_User_Seek,
        OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],
        'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'
        + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') +
        CASE
        WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN '_'
        ELSE ''
        END
        + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')
        + ']'
        + ' ON ' + dm_mid.statement
        + ' (' + ISNULL (dm_mid.equality_columns,'')
        + CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN ',' ELSE
        '' END
        + ISNULL (dm_mid.inequality_columns, '')
        + ')'
        + ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
    FROM sys.dm_db_missing_index_groups dm_mig
    INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
    ON dm_migs.group_handle = dm_mig.index_group_handle
    INNER JOIN sys.dm_db_missing_index_details dm_mid
    ON dm_mig.index_handle = dm_mid.index_handle
    WHERE dm_mid.database_ID = DB_ID()
    ORDER BY Avg_Estimated_Impact DESC
    GO

            

View database size

SELECT file_id, name, type_desc, physical_name, (size*8)/1024 as size_MB, size, (max_size*8)/1024 as max_size_MB, max_size  
FROM sys.database_files

View database size left

SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB FROM sys.database_files

View table sizes

SELECT 
    s.Name AS SchemaName,
    t.NAME AS TableName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM 
    sys.tables t
INNER JOIN 
    sys.schemas s ON s.schema_id = t.schema_id
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
WHERE 
    t.NAME NOT LIKE 'dt%'    -- filter out system tables for diagramming
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    s.Name, t.Name

Resize Log File

Get log file name

SELECT @log_File_Name = name FROM sysfiles WHERE filename LIKE '%LDF'

Shink the log file to 1 MB

DBCC SHRINKFILE (N'namehere_log' , 1)

Might have to do 'SET RECOVERY SIMPLE' / 'SET RECOVERY FULL' before and after DBCC call

SQL Join Explanation

Visual Explanation of SQL Joins

Finding Time Query takes in stored procedures

DECLARE @_StartTime datetime
DECLARE @_EndTime datetime

SET @_StartTime  = getdate()
-------------
-- QUERY 1
-------------
SELECT * FROM TABLE1
SET @_EndTime  = getdate()
print 'Timecheck: Query 1 Results: ' + CAST(CAST(@_EndTime - @_StartTime as TIME) as VARCHAR(100))
SET @_StartTime = getdate()

-------------
-- QUERY 2
-------------
SELECT * FROM TABLE2
SET @_EndTime  = getdate()
print 'Timecheck: Query 2 Results: ' + CAST(CAST(@_EndTime - @_StartTime as TIME) as VARCHAR(100))

            

How to identify the most costly SQL Server queries using DMV’s

--John Sansom: https://www.johnsansom.com/how-to-identify-the-most-costly-sql-server-queries-using-dmvs/
SELECT TOP 20
    qs.sql_handle,
    qs.execution_count,
    qs.total_worker_time AS Total_CPU,
    total_CPU_inSeconds = --Converted from microseconds
        qs.total_worker_time/1000000,
    average_CPU_inSeconds = --Converted from microseconds
        (qs.total_worker_time/1000000) / qs.execution_count,
    qs.total_elapsed_time,
    total_elapsed_time_inSeconds = --Converted from microseconds
        qs.total_elapsed_time/1000000,
    st.text,
    qp.query_plan
FROM
    sys.dm_exec_query_stats AS qs
CROSS APPLY 
    sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY
    sys.dm_exec_query_plan (qs.plan_handle) AS qp
ORDER BY 
    qs.total_worker_time DESC
            

See running queries

-- Saadat: In the fields of status, wait_type and cpu_time you can find the most cpu consuming task that is running right now.
SELECT s.session_id,
    r.status,
    r.blocking_session_id 'Blk by',
    r.wait_type,
    wait_resource,
    r.wait_time / (1000 * 60) 'Wait M',
    r.cpu_time,
    r.logical_reads,
    r.reads,
    r.writes,
    r.total_elapsed_time / (1000 * 60) 'Elaps M',
    Substring(st.TEXT,(r.statement_start_offset / 2) + 1,
    ((CASE r.statement_end_offset
WHEN -1
THEN Datalength(st.TEXT)
ELSE r.statement_end_offset
END - r.statement_start_offset) / 2) + 1) AS statement_text,
    Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid, st.dbid)) + N'.' +
    Quotename(Object_name(st.objectid, st.dbid)), '') AS command_text,
    r.command,
    s.login_name,
    s.host_name,
    s.program_name,
    s.last_request_end_time,
    s.login_time,
    r.open_transaction_count
FROM sys.dm_exec_sessions AS s
    JOIN sys.dm_exec_requests AS r
ON r.session_id = s.session_id
    CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id != @@SPID
ORDER BY r.cpu_time desc