Alcuni comandi utili da utilizzare in un server MSSQL.

-- Visualizza l'elenco dei server disponibili, locale e collegati
sp_helpserver

-- List of databases
SELECT * FROM master..sysdatabases
WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb')
-- or
 EXEC sp_databases

-- show List of Tables for the current db
USE myDB
GO
SELECT name FROM sys.Tables
GO

-- Per rinominare un server con istanza autonoma
sp_dropserver 'CURRENTNAME';
GO
sp_addserver 'NEWNAME', local;
GO

-- permette di esequire OPENQUERY sul server locale
exec sp_serveroption @server = 'SERVERNAME' 
,@optname = 'DATA ACCESS' 
,@optvalue = 'TRUE'

-- Create an unique name for a temp table
DECLARE @tempTable varchar(255) = 'myprefix_' + convert(varchar(36),NEWID())
-- select @tempTable 
-- build query
    SET @Sql = 
        'SELECT * INTO [##' + @tempTable  + '] FROM myTable'
EXEC (@Sql)
EXEC ('SELECT * FROM [##' + @tempTable + '] ')
EXEC ('DROP TABLE [##' + @tempTable + ']')
PRINT 'Dropped Table ' + @tempTable 

-- Create a simple new counter columns
SELECT 
 ROW_NUMBER() OVER (ORDER BY my_order_column) AS Row_Counter
 , my_first_column
 , my_second_column,
   
FROM my_table
-- Create a grouped new counter columns
-- Counter will be reset when Country change
SELECT 
ROW_NUMBER() OVER (PARTITION BY Country, Country ORDER BY Country) AS Row_Counter
, Country
, City
from CountryTable
 order by Country
 
 
 -- Create a grouped new counter columns
 -- Counter will be reset when Country change
SELECT 
ROW_NUMBER() OVER (PARTITION BY Country, City ORDER BY Country, City) AS Row_Counter
, Country
, City
from CountryTable
 order by Country, City

--- Create Role and assign the execute to it and the to the user
Use myDatabase
go
-- The role will be created in the current database "myDatabase"
CREATE ROLE db_executor
-- Grant the execute to the role
GRANT EXECUTE ON SCHEMA::dbo TO db_executor
-- assin the new role db_executor to the specified user
exec sp_addrolemember 'db_executor', 'myuser'

SHOW COLLECTION OF ALL DATABASES

--Verify the collation setting for all databases
SELECT name, collation_name
FROM sys.databases
order by collation_name

CHECK IF OBJECT EXISTS

-- A way to test if a database exists
USE master;
GO
IF DB_ID (N'myDB') IS NOT NULL
DROP DATABASE myDB;
GO
-- A way to test if a Table exists
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[myDB]') AND type in (N'U'))
DROP TABLE dbo.myDB
-- or another could be is...
IF NOT EXISTS(SELECT Distinct TABLE_NAME FROM information_schema.TABLES Where TABLE_NAME = 'Your_Table')
BEGIN 
--CREATE TABLE 
Your_Table
END
-- a way to test if a view exists
IF OBJECT_ID('myDB.dbo.myView', 'V') IS NOT NULL
    DROP VIEW myView;
GO

--Check if it exists
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
    BEGIN
        PRINT '#temp exists!'
    END
ELSE
    BEGIN
        PRINT '#temp does not exist!'
    END

-- Rebuild index for the database

USE myDB
 GO
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
 GO
EXEC sp_updatestats
GO

-- Rebuild index for all database excluding some
exec sp_MSforeachdb '
IF ''!'' NOT IN (''master'',''model'',''msdb'',''tempdb'')
BEGIN
USE [!]; 
EXEC sp_MSforeachtable ''ALTER INDEX ALL ON ? REBUILD''
END',@replacechar = '!'

-- Read all database excluding status ...
SELECT name FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb','distrbution')    -- exlude system databases
 AND status &  -- exclude statuses not suitable for reindexing
 (   32 -- loadig
  |  64 -- pre recovery
  | 128 -- recovering
  | 256 -- not recovered
  | 512 -- offline
  |1024 -- readonly
  |2048 -- dbo use only
  |4096 -- single user
  |32768 -- emergency mode
  ) = 0

 -- Allow to print more chars

PRINT CAST(@SQLALL AS NTEXT)

 Search substring starting from right

/*
GOAL: get myfilename.xls from the following fullpath
c:\myfolder1\myfolder2\myfilename.xls
*/
SELECT right([FILENAME], charindex('\',reverse(FILENAME))-1)  FROM [ICT].[dbo].[file2]

Store into a var a list of values returned from a select

-- Store a list of key values returned from a select
declare @progrList nvarchar(max)
set @progrList= ''
select @progrList= @progrList+ convert(varchar(6), myColKey) + ', ' 
from myTable 
print @resULT

 SQL variable to hold list of integers

I found this very great solution to  stackoverflow

declare @listOfIDs table (id int);
insert @listOfIDs(id) values(1),(2),(3);    

select *
from TabA
where TabA.ID in (select id from @listOfIDs)

or

declare @listOfIDs varchar(1000);
SET @listOfIDs = ',1,2,3,'; --in this solution need put coma on begin and end

select *
from TabA
where charindex(',' + CAST(TabA.ID as nvarchar(20)) + ',', @listOfIDs) > 0

Send e-mail

 You can send an e-mail calling a system store procedure. useful to notify an error or sending periodical results of select

note: you need to configure the "e-mail" on your MSSQL server.

-- Send an e-mail using T-SQL
    USE msdb
    EXEC sp_send_dbmail
      @profile_name = 'YourProfileMailConfiguredOnYourServer',
      @recipients = 'youraddress@yourdomain.com',
      @subject = 'Please check the records attached',
      @body = 'The result from SELECT:',
      @execute_query_database = 'msdb',
      @query = 'SELECT myCol1, myCol2 FROM myTable'

-- You can use the following to append a newline
@query = "PRINT CHAR(13)+'Use double-quotation marks.' + CHAR(13) + 'Start a newline'"

 EXPORT TO EXCEL

-- Exec a select from command line
-- You need to configure your server to allow it
Exec xp_cmdshell 'bcp "Select * from [Control08].[dbo].[xAnva200F]" queryout "C:\testing.xls" -c -T'


--- HOW TO CONFIGURE THE SERVER TO ALLOW A SHELL COMMAND
-- => xp_cmdshell

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

 COLUMN NOT SORTABLE

-- BEFORE => Not Ordered Data
SELECT * 
FROM (
	SELECT 'BT-1'	AS Col  UNION ALL
	SELECT 'BT-2'	AS Col	UNION ALL
	SELECT 'BT-10'	AS Col	UNION ALL
	SELECT 'BT-11'	AS Col	UNION ALL
	SELECT 'BT-100'	AS Col	UNION ALL
	SELECT 'BT-101'	AS Col		
	) AS myTable
ORDER BY
	Col

-- AFTER => Data Sorted
SELECT Col, LEFT(Col, LEN(Col) - CHARINDEX('-', REVERSE(Col))) + '-' + REPLICATE('0', 7 - LEN(right(Col,charindex('-',reverse(Col))-1)) ) + right(Col,charindex('-',reverse(Col))-1)  AS ColSortable 
	FROM (
		SELECT 'BT-1'	AS Col  UNION ALL
		SELECT 'BT-2'	AS Col	UNION ALL
		SELECT 'BT-10'	AS Col	UNION ALL
		SELECT 'BT-11'	AS Col	UNION ALL
		SELECT 'BT-100'	AS Col	UNION ALL
		SELECT 'BT-101'	AS Col		
	) AS myTable
ORDER BY
	ColSortable

COMMIT / ROLLBACK

I know 2 ways:

• General Options for your Client
• SQL Script for Single Tansaction

General Option for your Client

You can set a general options in your Microsoft SQL Server Management Studio.

• Tools -> Options
• Query Execution -> SQL Server -> ANSI => SET IMPLICIT_TRANSACTION  "Checked"

SQL Script for Single Tansaction

-- Option ON
SET IMPLICIT_TRANSACTIONS ON

--- Your update
UPDATE myTable SET myfield=1 where...

-- Performe a Rollback OR a Commit
ROLLBACK
COMMIT

-- Option OFF
SET IMPLICIT_TRANSACTIONS OFF

TABLE VARIABLE IN A DYNAMIC STATEMENT

I read a couple of solutions on StackOverflow.com. One is to replace the table variable with a temp table but it's not the right reply to the question. The second solution it's a bit complicated but not so much but it's possible only with mssql 2008 or superior.

I just copied and pasted here the example

CREATE TYPE MyTable AS TABLE 
( 
Foo int,
Bar int
);
GO


DECLARE @T AS MyTable;

INSERT INTO @T VALUES (1,2), (2,3)

SELECT *,
        sys.fn_PhysLocFormatter(%%physloc%%) AS [physloc]
FROM @T

EXEC sp_executesql
  N'SELECT *,
        sys.fn_PhysLocFormatter(%%physloc%%) AS [physloc]
    FROM @T',
  N'@T MyTable READONLY',
  @T=@T 

•  stackoverflow.com

REFERENCES

• http://www.sommarskog.se/dynamic_sql.html

...to be continue...