sql misc
xact_abort
SET xact_abort ON
Update statistics on all tables
DECLARE @currentTableName nvarchar(max) = ''
DROP TABLE IF EXISTS #tableListUpdateStats
SELECT '[' + table_schema + '].[' + table_name + ']' as TableNameFull INTO #tableListUpdateStats
FROM information_schema.tables WHERE TABLE_TYPE = 'BASE TABLE' ORDER BY table_schema, table_name
WHILE 1=1
BEGIN
SET @currentTableName = (select top(1) TableNameFull from #tableListUpdateStats where TableNameFull > @currentTableName order by TableNameFull)
IF @currentTableName IS NULL BREAK
DECLARE @sqlCommand nvarchar(max) = 'UPDATE STATISTICS ' + @currentTableName + ' '
PRINT @sqlCommand
EXEC sp_executesql @sqlCommand
END
DROP TABLE IF EXISTS #tableListUpdateStats
SQLCMD input parameters
Using the -v option, we can pass parameters to a SQL script invoked by SQLCMD. The parameters can then be used inside the sql script using the $(param) syntax.
sqlcmd -S (LocalDb)\MSSQLLocalDB -d MyDB -i myscript.sql -v varCD="%CD%"
PRINT 'CD: ' + '$(varCD)'
SET @csvCurrPath = '$(varCD)' + '\..\..\Data\OUT\data.csv'