Dicas T-SQL.sql

-- Pegar a data removendo a HH:MM:SS:mmm
SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
-- ou
SELECT convert(DATETIME, floor(convert(FLOAT(24), GETDATE())))

-- SQL Server Date Formats (http://www.sql-server-helper.com/tips/date-formats.aspx)
SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 103), '/', '') AS [DDMMYYYY]

-- Convert rows in concatenaded string list
SELECT STUFF((SELECT ',''' + field + ''''
              FROM table
              FOR XML PATH ('')),1,1,'')
              
-- Query para ver o nível de fragmentação
SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID() AND dbtables.[name] like '%%'
ORDER BY indexstats.avg_fragmentation_in_percent desc

-- Comando para reconstruir os indices de todas tabelas
EXEC sp_msforeachtable 'SET QUOTED_IDENTIFIER ON; ALTER INDEX ALL ON ? REBUILD'
                                       
-- Criar incluir usuário em todos bancos do servidor
-- https://www.mssqltips.com/sqlservertip/3541/grant-user-access-to-all-sql-server-databases/
Use master
GO

DECLARE @dbname VARCHAR(50)   
DECLARE @statement NVARCHAR(max)

DECLARE db_cursor CURSOR 
LOCAL FAST_FORWARD
FOR  
SELECT name
FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb','distribution')  
OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @dbname  
WHILE @@FETCH_STATUS = 0  
BEGIN  

SELECT @statement = 'use '+@dbname +';'+ 'CREATE USER [TipsDemoUser] 
FOR LOGIN [TipsDemoUser]; EXEC sp_addrolemember N''db_datareader'', 
[TipsDemoUser];EXEC sp_addrolemember N''db_datawriter'', [TipsDemoUser]'

exec sp_executesql @statement

FETCH NEXT FROM db_cursor INTO @dbname  
END  
CLOSE db_cursor  
DEALLOCATE db_cursor