working on it ...

Filters

snippets
16
followers
18
Published by cghersi

MS SQL Server Tools

SQL Server is not only the best DBMS available on the market, but can be effectively used to provide several capabilities to your 3-tier applications. Discover how here...
Sort by

Found 16 snippets

    public by DinhoPutz  413  0  4  0

    Remover caracteres especiais e quebra-linhas de um resultado em uma tabela

    Substituir o texto, na verdade, por outra coisa, seja por nada ou por um espaço vazio, talvez. Ideal para resolver aqueles problema na exportação de um resultado para CSV para deve ser aberto no Excel e aquele maldito ponto e vírgula (;) acaba levando parte do texto para outra coluna. Aqui também removemos tabulações, quebralinhas e outros.
    SELECT
    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(nomeDaColuna,CHAR(13) + Char(10) ,''), CHAR(10), ''), CHAR(13), ''), CHAR(9), ''),';','-'),'  ', ' ') as 'Um nome novo e legal' 
    FROM NOMEDATABELA

    public by cghersi  4159  7  7  0

    Check the missing indexes in SQL Server

    This query returns the list of missing indexes for the queries that are currently running on the server
    SELECT 
    	er.session_id,
    	er.blocking_session_id,
    	er.start_time,
    	er.status,
    	dbName = DB_NAME(er.database_id),
    	er.wait_type,
    	er.wait_time,
    	er.last_wait_type,
    	er.granted_query_memory,
    	er.reads,
    	er.logical_reads,
    	er.writes,
    	er.row_count,
    	er.total_elapsed_time,
    	er.cpu_time,
    	er.open_transaction_count,
    	s.text,
    	qp.query_plan,
    	logDate = CONVERT(DATE,GETDATE()),
    	logTime = CONVERT(TIME,GETDATE())
    FROM sys.dm_exec_requests er 
    CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) s
    CROSS APPLY sys.dm_exec_query_plan(er.plan_handle) qp
    WHERE 
    	CONVERT(VARCHAR(MAX), qp.query_plan) LIKE '%<missing%'            

    public by cghersi  4773  1  6  1

    Remove Duplicate rows in SQL Server

    This script removes the duplicate rows in table Customers detecting the rows with the same contactname
    SET NOCOUNT ON
    SET ROWCOUNT 1
    WHILE 1 = 1
      BEGIN
        DELETE  
        FROM Customers
        WHERE contactname IN
            (SELECT  contactname
             FROM    Customers
             GROUP BY contactname
             HAVING  COUNT(*) > 1)
          
        IF @@Rowcount = 0
          BREAK ;
     END
     SET ROWCOUNT 0          

    public by cghersi  3567  3  6  1

    Check Recovery status of DB in SQL Server 2012

    This query allows you to check the status f your recovery in SQL Server. More information on http://timlaqua.com/2009/09/determining-how-long-a-database-will-be-in-recovery-sql-server-2008/
    DECLARE @DBName VARCHAR(64) = 'databasename'
     
    DECLARE @ErrorLog AS TABLE([LogDate] CHAR(24), [ProcessInfo] VARCHAR(64), [TEXT] VARCHAR(MAX))
     
    INSERT INTO @ErrorLog
    EXEC master..sp_readerrorlog 0, 1, 'Recovery of database', @DBName
     
    SELECT TOP 5
    	 [LogDate]
    	,SUBSTRING([TEXT], CHARINDEX(') is ', [TEXT]) + 4,CHARINDEX(' complete (', [TEXT]) - CHARINDEX(') is ', [TEXT]) - 4) AS PercentComplete
    	,CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0 AS MinutesRemaining
    	,CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0/60.0 AS HoursRemaining
    	,[TEXT]
     
    FROM @ErrorLog ORDER BY [LogDate] DESC         

    public by cghersi  2747  9  6  3

    Retrieve the SQL commands that are currently executing in MS SQL Server

    Thanks to avinash.kote http://www.sqlservercentral.com/scripts/118732/
    SELECT  des.session_id, des.status, des.login_name, des.[HOST_NAME],
            der.blocking_session_id, DB_NAME(der.database_id) as database_name,
            der.command, des.cpu_time, des.reads, des.writes, dec.last_write,
            des.[program_name], emg.requested_memory_kb, emg.granted_memory_kb,
            emg.used_memory_kb, der.wait_type, der.wait_time, der.last_wait_type,
            der.wait_resource,
            CASE des.transaction_isolation_level 
              WHEN 0 THEN 'Unspecified' 
              WHEN 1 THEN 'ReadUncommitted' 
              WHEN 2 THEN 'ReadCommitted'
              WHEN 3 THEN 'Repeatable' 
              WHEN 4 THEN 'Serializable' 
              WHEN 5 THEN 'Snapshot' END AS transaction_isolation_level,
            OBJECT_NAME(dest.objectid, der.database_id) as OBJECT_NAME, 
            dest.text as full_query_text,
            SUBSTRING(dest.text, der.statement_start_offset /2,
              (CASE WHEN der.statement_end_offset = -1 THEN DATALENGTH(dest.text) 
                    ELSE der.statement_end_offset END - 
              der.statement_start_offset) / 2
            ) AS [executing_statement], deqp.query_plan
    FROM  sys.dm_exec_sessions des
    LEFT JOIN sys.dm_exec_requests der on des.session_id = der.session_id
    LEFT JOIN sys.dm_exec_connections dec on des.session_id = dec.session_id
    LEFT JOIN sys.dm_exec_query_memory_grants emg on des.session_id = emg.session_id      
    CROSS APPLY sys.dm_exec_sql_text(der.sql_handle) dest
    CROSS APPLY sys.dm_exec_query_plan(der.plan_handle) deqp
    WHERE des.session_id <> @@SPID
    ORDER BY  des.session_id                      

    public by cghersi  4139  4  6  0

    Query XML fields' content in MS SQL Server

    This is the right syntax to perform XQuery queries for XML columns in a SQL Server table
    SELECT MyField.query('
      declare namespace s2c="http://www.snip2code.com/namespaces/s2c";
    	(/s2c:myroot/myelem/fieldIwant[1]/text())')
    FROM  MyTable

    public by cghersi  4289  6  6  2

    Move a file to another location in SQL Server

    Here is the procedure to move a file to another location (e.g. for the database called MyDB). First you need to execute the first part of this snippet (until the comment '/**** Now you need to physically move the file to the other location ***/'). Then you need to move the file and eventually you can execute the last two statements.
    ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    
    ALTER DATABASE MyDB SET OFFLINE;
    
    ALTER DATABASE MyDB MODIFY FILE 
    (
       Name = MyDB_file1,
       Filename = 'c:\DB\MyDB.mdf'
    );
    
    /**** Now you need to physically move the file to the other location ***/
    
    ALTER DATABASE MyDB SET ONLINE;
    
    ALTER DATABASE MyDB SET MULTI_USER;

    public by cghersi  2701  0  6  0

    How to Retrieve all the tables having indexes on a specific filegroup in SQL Server

    Here are listed all the tables that have one or more indexes stored in a filegroup called 'INDEXES'
    SELECT 
        distinct  TableName = t.name
    FROM 
         sys.indexes ind 
    INNER JOIN 
         sys.index_columns ic ON  ind.object_id = ic.object_id and ind.index_id = ic.index_id 
    INNER JOIN 
         sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id 
    INNER JOIN 
         sys.tables t ON ind.object_id = t.object_id 
    INNER JOIN sys.[filegroups] f
        ON f.[data_space_id] = ind.data_space_id
    WHERE f.name = 'INDEXES'
    

    public by cghersi  2338  2  6  0

    Find columns used in indexes for SQL Server

    Retrieves a number of information related to the structure of the available indexes
    SELECT 
         TableName = t.name,
         IndexName = ind.name,
         ColumnName = col.name, FGName = f.name,
    	 ic.is_descending_key, ic.is_included_column, ind.is_unique, ind.type_desc, ic.*
    FROM 
         sys.indexes ind 
    INNER JOIN 
         sys.index_columns ic ON  ind.object_id = ic.object_id and ind.index_id = ic.index_id 
    INNER JOIN 
         sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id 
    INNER JOIN 
         sys.tables t ON ind.object_id = t.object_id 
    INNER JOIN sys.[filegroups] f
        ON f.[data_space_id] = ind.data_space_id
    WHERE ind.is_unique_constraint = 0  AND t.is_ms_shipped = 0 
    ORDER BY 
         t.name, ind.name, ind.index_id, ic.index_column_id

    public by cghersi  3218  5  6  2

    Detect the worst SQL queries in avg CPU time in SQL Server

    Detect worst performing sql queries which is slowing down Microsoft SQL Server, this script return top queries taxing sql server CPUs. Thanks to Patrick Akhamie: http://www.sqlservercentral.com/scripts/TOP+CPU+Queries/110012/
    use tempdb
    go
    
    IF object_id('tempdb..##FindTopCPUQueries_set1') is not null DROP TABLE [dbo].[##FindTopCPUQueries_set1]
    GO
    
    declare @ServerTime datetime = getdate()
    
    , @ConvertMiliSeconds bigint = 1000
    
    , @FilterMoreThanMiliSeconds bigint = 1
    
    , @FilterHours bigint = 2
    
    , @execution_count bigint = 2
    
    , @debugFlg bit = 0
    
    
    
    if @debugFlg=1 select @ServerTime as ServerTime, @ConvertMiliSeconds as ConvertMiliSeconds
    
    , @FilterMoreThanMiliSeconds as FilterMoreThanMiliSeconds, @FilterHours as FilterHours 
    
    , @execution_count as execution_count
    
    
    
    
    
    select TOP 300
    
    @@servername as servername,@ServerTime as runtime
    
    ,isnull(db_name(QueryText.dbid),'PreparedSQL') as DBName 
    
     ,SUBSTRING(QueryText.text, (QueryStats.statement_start_offset/2)+1, 
    
    (isnull((
    
    CASE QueryStats.statement_end_offset
    
     WHEN -1 THEN DATALENGTH(QueryText.text)
    
     WHEN 0 THEN DATALENGTH(QueryText.text)
    
     ELSE QueryStats.statement_end_offset
    
     END - QueryStats.statement_start_offset),0)/2) 
    
     + 1) AS QueryExecuted
    
    ,total_worker_time AS total_worker_time
    
    ,QueryStats.execution_count as execution_count
    
    ,statement_start_offset,statement_end_offset
    
    ,(case when QueryText.dbid is null then OBJECT_NAME(QueryText.objectid) else OBJECT_NAME(QueryText.objectid, QueryText.dbid) end) as ObjectName
    
    ,query_hash
    
    ,plan_handle
    
    ,sql_handle
    
    into ##FindTopCPUQueries_set1
    
    from sys.dm_exec_query_stats as QueryStats
    
    cross apply sys.dm_exec_sql_text(QueryStats.sql_handle) as QueryText
    
    where QueryStats.query_hash IN 
    
    (
    
    select QueryStatsBaseTable.query_hash 
    
    from sys.dm_exec_query_stats QueryStatsBaseTable
    
    where last_execution_time > DATEADD(hh,-@FilterHours,GETDATE())
    
    group by query_hash
    
    having (sum(total_worker_time)/sum(execution_count))>@ConvertMiliSeconds and sum(execution_count)>@execution_count
    
    )
    
    ORDER BY total_worker_time/execution_count DESC;
    
    
    
    if @debugFlg=1 select * from ##FindTopCPUQueries_set1 order by QueryExecuted
    
    
    
    IF object_id('tempdb..##FindTopCPUQueries_set2') is not null DROP TABLE [dbo].[##FindTopCPUQueries_set2]
    
    
    
    select 
    
    servername,runtime,max(DBName) as DBName,max(QueryExecuted) as QueryExecuted,(sum(total_worker_time)/sum(execution_count))/@ConvertMiliSeconds as AvgCPUTime
    
    ,sum(execution_count) as execution_count,query_hash, max(ObjectName) as ObjectName
    
    into ##FindTopCPUQueries_set2
    
    from ##FindTopCPUQueries_set1
    
    group by query_hash,servername,runtime
    
    order by AvgCPUTime desc
    
    
    
    
    
    select * from ##FindTopCPUQueries_set2
    
    --where QueryExecuted like 'select TOP 300%'
    
    order by AvgCPUTime desc
    • Public Snippets
    • Channels Snippets