/ notes / sql

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'