working on it ...

Filters

Explore Public Snippets

Sort by

Found 20 snippets

    public by DinhoPutz  403  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  4152  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  2705  1  6  3

    How to query some values inside XML in SQL Server

    This query relies on 'value' operator applied on XML fields. You can directly transform the resulting value into a particular type with the second parameter of this function.
    WITH XMLNAMESPACES ('http://www.snip2code.com/namespaces/s2c' AS s2c)
    SELECT  field1, field2
    FROM    MyTable
    WHERE   MyXMLField.value('/s2c:root/fieldName != ""', 'bit') = 1      
    
    WITH XMLNAMESPACES ('http://www.snip2code.com/namespaces/s2c' AS s2c)
    SELECT  field1, field2
    FROM    MyTable
    WHERE   MyXMLField.value('(/s2c:root/fieldName/text())[1]', 'varchar(100)') = 'some text'

    public by cghersi  3308  2  6  1

    Retrieve the domain of an email in MS SQL Server

    Suppose you have a table with an 'Email' field: here is the way to retrieve just only the domain of that email, and the name of the domain without the extension (.com, .net, etc.)
    select 
      SUBSTRING(Email, 
        charindex('@', Email, 0) + 1, 
        (charindex('.', Email, charindex('@', Email, 0)) - charindex('@', Email, 0)) - 1
      ) as domainWithNoExt, 
      SUBSTRING(Email, charindex('@', Email, 0) + 1, 200) as domain
    from MyTable

    public by cghersi  4134  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  4278  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  2697  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  2333  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  4952  3  6  0

    Recreate the index structure of an MS SQL Server DB in T-SQL

    This script prepares and executes several dynamic SQL statements to recreate indexes exactly as they are built on the original DB
    USE Snip
    GO
    
    DECLARE @indexName nvarchar(70), @tableName nvarchar(100), @sql nvarchar(4000) = '', @colName nvarchar(100), @desc bit, 
    	@inclCol bit, @prevInclCol bit,@unique bit, @typeDesc nvarchar(40), @previousIndexName nvarchar(70) = '',
    	@filegroup nvarchar(100) = '[PRIMARY]', @fgName nvarchar(100), @prevFGName nvarchar(100)
    
    DECLARE fk CURSOR FOR 
    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
    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
    
    OPEN fk
    FETCH NEXT FROM fk INTO @tableName, @indexName, @colName, @fgName, @desc, @inclCol, @unique, @typeDesc
    WHILE @@fetch_status = 0
    BEGIN
    	IF (@prevFGName = '')
    		SET @prevFGName = @fgName;
    print 'prev=' + @previousIndexName + ';new=' + @indexName + ';tbl=' + @tableName + ';col=' + @colName + 
    	';desc=' + CAST(@desc as nvarchar(10)) + ';fgName=' + @fgName + ';filegroup=' + @filegroup +
    	';include=' + CAST(@inclCol as nvarchar(10)) + ';unique=' + CAST(@unique as nvarchar(10)) + ';type=' + @typeDesc
    	IF (@previousIndexName = @indexName)
    	BEGIN
    		--continue with the previous index:
    		if (@inclCol = 1 AND @prevInclCol = 0)
    			SET @sql += ') INCLUDE ( ' + @colName;
    		ELSE
    			SET @sql += ',' + @colName;
    
    		SET @prevInclCol = @inclCol;
    		print 'sql in equal=' + @sql
    	 END
    	 ELSE
    	 BEGIN
    		--complete and exec the last statement:
    		SET @prevInclCol = 0;
    		IF (LEN(@sql) != 0)
    		BEGIN
    			IF (@prevFGName != @filegroup)
    			BEGIN
    				SET @sql += ') ON ' + @filegroup + ';';
    				--SET @sql += ') WITH (DROP_EXISTING = ON) ON ' + @filegroup + ';';
    				print 'sql to exec=' + @sql
    				EXEC( @sql);
    			END
    		END
    
    		--create a new statement:
    		SET @sql = ' CREATE ';
    		IF (@unique = 1)
    			SET @sql += ' UNIQUE ';
    		SET @sql += @typeDesc + ' INDEX ' + @indexName + '_1 ON dbo.' + @tableName + ' (' + @colName;
    		print 'sql created=' + @sql
    	END
    
    	IF (@inclCol = 0)
    	BEGIN
    		IF (@desc = 1)
    			SET @sql += ' DESC ';
    		ELSE
    			SET @sql += ' ASC ';
    	END
    	SELECT @previousIndexName = @indexName, @prevFGName = @fgName 
    				
    	FETCH NEXT FROM fk INTO @tableName, @indexName, @colName, @fgName, @desc, @inclCol, @unique, @typeDesc
    END
    CLOSE fk
    DEALLOCATE fk
    
    --complete and exec the last statement:
    IF (LEN(@sql) != 0)
    BEGIN
    	IF (@prevFGName != @filegroup)
    	BEGIN
    		SET @sql += ') ON ' + @filegroup + ';';
    		--SET @sql += ') WITH (DROP_EXISTING = ON) ON ' + @filegroup + ';';
    		print 'sql to exec=' + @sql
    		EXEC( @sql);
    	END
    END
    

    public by cghersi  3834  0  6  3

    How to clean all the data from the tables of a MS SQL Server database

    This procedure allows to clean all the data from all the tables of the given DB. Use @rese_ident = 1 if you want to restart the identity columns from the default value.
    CREATE PROCEDURE [dbo].[CleanDB]
    	@db_name sysname = 'MyDBName',
    	@reset_ident tinyint = 0
    as
    begin
    	set nocount on
    
    	if @db_name is null
    		set @db_name = db_name()
    
    	if @reset_ident is null
    		set @reset_ident = 0
    
    	if not exists(select * from sys.databases where name = @db_name and database_id > 4)
    	begin
    		raiserror('Database does not exist or it can not be cleared', 16, 1)
    		return
    	end
    
    	declare @recovery_mode sysname
    
    	select @recovery_mode = cast(DatabasePropertyEx(@db_name,'Recovery') as sysname)
    
    	if @recovery_mode <> 'Simple'
    	begin
    		declare @ncmd nvarchar(max)
    
    		print '-- Changing database recovery mode to SIMPLE'
    
    		set @ncmd = 'alter database [' + @db_name + '] set recovery simple'
    
    		print @ncmd
    
    		exec sp_executesql @ncmd
    	end
    
    	-------------------------------------------------------------------------------------------
    	-- prepare table with tables list
    
    	create table #temp_tables
    	(
    		rec_id int identity(1, 1) primary key not null,
    		schema_name sysname not null,
    		table_name sysname not null
    	)
    
    	declare @n_cmd nvarchar(max)
    
    	set @n_cmd = 'insert into #temp_tables (schema_name, table_name) select ss.name as schema_name, st.name as table_name from [' + @db_name + '].sys.tables as st inner join [' + @db_name + '].sys.schemas as ss on ss.schema_id = st.schema_id where ss.name <> ''sys'' '
    
    	exec sp_executesql @n_cmd
    
    	-------------------------------------------------------------------------------------------
    	-- disable constraints
    
    	declare @table_name sysname
    	declare @schema_name sysname
    	declare @counter_max int
    	declare @counter int
    
    	select @counter_max = max(rec_id) from #temp_tables
    
    	if @counter_max is null
    		set @counter_max = 0
    
    	declare @object_name nvarchar(max)
    
    	set @counter = @counter_max
    	while @counter > 0
    	begin
    		set @table_name = null
    		set @schema_name = null
    
    		select @table_name = table_name, @schema_name = schema_name from #temp_tables where rec_id = @counter
    
    		if @table_name is null or @schema_name is null
    			break
    
    		set @object_name = N'[' + @db_name + N'].[' + @schema_name + N'].[' + @table_name + N']'
    
    		set @n_cmd = N'alter table ' + @object_name + N' nocheck constraint all'
    
    		print @n_cmd
    
    		begin try
    			exec sp_executesql @n_cmd
    		end try
    		begin catch
    			print '-------------------------------------------------------------------------'
    			print 'ERROR - Could not disable constraints for table ' + @object_name
    			print error_message()
    			print '-------------------------------------------------------------------------'
    		end catch
    
    		set @counter = @counter - 1
    	end
    
    	----------------------------------------------------------------------------------------------
    	-- delete records from tables
    
    	set @counter = @counter_max
    	while @counter > 0
    	begin
    		set @table_name = null
    		set @schema_name = null
    
    		select @table_name = table_name, @schema_name = schema_name from #temp_tables where rec_id = @counter
    
    		if @table_name is null or @schema_name is null
    			break
    
    		set @object_name = N'[' + @db_name + N'].[' + @schema_name + N'].[' + @table_name + N']'
    
    		set @n_cmd = 'delete ' + @object_name
    
    		print @n_cmd
    
    		begin try
    			exec sp_executesql @n_cmd
    
    			if @reset_ident = 1
    			begin
    				set @n_cmd = 'if exists(select * from [' + @db_name + '].sys.columns where object_id = object_id(''' + 
    					@object_name + ''') and is_identity = 1) dbcc checkident(''' + @object_name + ''', reseed)'
    
    				print @n_cmd
    
    				exec sp_executesql @n_cmd
    			end
    		end try
    		begin catch
    			print '-------------------------------------------------------------------------'
    			print 'ERROR - Could not clean table ' + @object_name
    			print error_message()
    			print '-------------------------------------------------------------------------'
    		end catch
    
    		set @counter = @counter - 1
    	end
    
    	-----------------------------------------------------------------------------------------------
    	-- enable constraints
    
    	set @counter = @counter_max
    	while @counter > 0
    	begin
    		set @table_name = null
    		set @schema_name = null
    
    		select @table_name = table_name, @schema_name = schema_name from #temp_tables where rec_id = @counter
    
    		if @table_name is null or @schema_name is null
    			break
    
    		set @n_cmd = 'alter table [' + @db_name + '].[' + @schema_name + '].[' + @table_name + '] with check check constraint all'
    
    		print @n_cmd
    
    		begin try
    			exec sp_executesql @n_cmd
    		end try
    		begin catch
    			print '-------------------------------------------------------------------------'
    			print 'ERROR - Could not enable constraints for table ' + @object_name
    			print error_message()
    			print '-------------------------------------------------------------------------'
    		end catch
    
    		set @counter = @counter - 1
    	end
    
    	drop table #temp_tables
    
    	---------------------------------------------------------------------------------------------------
    	-- restore database recovery mode
    
    	if @recovery_mode <> 'Simple'
    	begin
    		declare @ncmd2 nvarchar(max)
    
    		print '-- Restoring database recovery mode'
    
    		set @ncmd2 = 'alter database [' + @db_name + '] set recovery ' + @recovery_mode
    
    		print @ncmd2
    
    		exec sp_executesql @ncmd2
    	end
    
    end
    • Public Snippets
    • Channels Snippets