Cacher is the code snippet organizer for pro developers

/* Connection */
/*

Connect to SQLSERVER (No Auth) : 
      sqlcmd -S DEHMOBILE-PD\SQLEXPRESS -E
Connect to SQLSERVER (UserName): 
      sqlcmd -S DESKTOP-5K4TURF\SQLEXPRESS -U jsmith
Connect to SQLSERVER (Password): 
      sqlcmd -S DESKTOP-5K4TURF\SQLEXPRESS -U jsmith -P Mypwd$%1234565
*/




/* comment */
select DB_NAME()
go

/* Check all databases on server */
/* Stored proc: Sp_databases */
/* 1> Sp_databases */
/* 2> go */
select name from sys.databases
go

/* Change database context */
/* Result: Changed database context to “master” */
use dbname;
go

/* Check SQLSERVER properties */
-- Collation:
1.  SELECT SERVERPROPERTY('COLLATION')
-- .Net Framework Version: 
1.	SELECT SERVERPROPERTY('BuildClrVersion')
-- Edition
1.	SELECT SERVERPROPERTY('Edition')
-- Engine Edition
1.	SELECT SERVERPROPERTY('EngineEdition')
-- Editions: [TPBEFILLEDFROMMSSITE]
1.	SELECT SERVERPROPERTY('EDITION')
--	Group Manager Active/Inactive
1.	SELECT SERVERPROPERTY('HadrManagerStatus')
-- Instance Default Data Path
1.	SELECT SERVERPROPERTY('InstanceDefaultDataPath')
-- Instance Default Log Path
1.	SELECT SERVERPROPERTY('InstanceDefaultLogPath')
-- Instance Name
1.	SELECT SERVERPROPERTY('InstanceName')
-- Integrated Security
1.	SELECT SERVERPROPERTY('IsIntegratedSecurityOnly')
a.	1 = Integrated (Windows Auth)
b.	0 = Non integrated (Windows Auth and Sql Server Auth)
-- Machine Name
1.	SELECT SERVERPROPERTY('MachineName')
-- Process ID
1.	SELECT SERVERPROPERTY('ProcessID')
-- Product Version
1.	SELECT SERVERPROPERTY('ProductVersion')


/*&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&*/
/* How to check sql server edition */
SELECT SERVERPROPERTY('EDITION')
Go
-- Result: Express Edition (64-bit)


/*&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&*/
/* How to find sql server authentification */
SELECT SERVERPROPERTY('IsIntegratedSecurityOnly')
Go
-- Result:  0 if both are enabled (Windows/SqlServerAuth)
--          1 if only Windows Authentification enabled.


/*&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&*/
/* How to find which schema a table belongs in a db */
-- We need the schema so we can reference it, ie <schema>.<tablename>
-- To find schema we need to get into context -> 
use dbname;
Select SCHEMA_NAME(schema_id) * from sys.objects
Where name = ‘myTable’.
go


/*&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&*/
/* -- List all environmental variables in system */
:ListVar
Go




/*&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&*/
/* Run .sql scripts in command mode */
-- Write the sql statement in a script and save it as .sql
-- Ex. Script: queryex.sql
-- Navigate to the directory (if you don’t want to use full path)
-- Open CMD as administrator
-- Sample with relative path:
1.	sqlcmd -S DEHMOBILE-PD\SQLEXPRESS -E -i .\queryex.sql -o .\queryexout.1.txt
-- Sample with absolute path:
1.	sqlcmd -S DEHMOBILE-PD\SQLEXPRESS -E -i C:\Users\panagiotis\Desktop\SQLServer\Info\queryex.sql -o .\queryexout.1.txt


/* Run STORED procedures in command mode with file output */
-- Write the db you use in a script file and save it as .sql
-- Ex.Script: InvokeSp.sql
-- Navigate to the directory (if you don’t want to use full path)
-- Open CMD as administrator
-- Sample with relative path:
sqlcmd -S DEHMOBILE-PD\SQLEXPRESS -E -i .\InvokeSp.sql -o .\InvokeSp.1.txt


/* How to run sql one liner from cmd and capture output */
sqlcmd -S DEHMOBILE-PD\SQLEXPRESS -E –Q “select top 100 * from people”-o .\InvokeSp.1.txt


/* How to back up in sqlcmd */
--Write the sql backup script 
--BackupDb.sql
--Run the sql backup script:
sqlcmd -S DEHMOBILE-PD\SQLEXPRESS -E -i .\BackupDb.sql -o .\BackupDblog.txt



/* How to set variables in sqlcmd (and sql scripts) */
--Use the :SETVAR
:SETVAR DBASENAME "adventureworks2014"
USE $(DBASENAME)
GO
--Result: Changed database context to 'AdventureWorks2014'.



/* How to run queries in sqlcmd */
use adventureworks2014
go
:SETVAR CONTACTTYPEID 3
SELECT [ContactTypeID]
,[Name]
,[ModifiedDate]
FROM [Person].[ContactType]
WHERE contacttypeid=$(CONTACTTYPEID)
Go



/* How to list the table names of a database in sqlcmd */
select table_name from adventureworks2014.information_schema.tables
go
select table_name from $(DBASENAME).information_schema.tables
go
-- The above could be entered into a script:
sqlcmd -S DEHMOBILE-PD\SQLEXPRESS -E -i .\ListTablesSchemaParam.sql.sql -v DBASENAME ="AdventureWorks2014" -o .\ListedTables.txt


/* How to get a good idea of a table schema */
How to get a good idea of a table schema 
lxxiv.	(table_schema, table_name, table_column)
lxxv.	select table_schema, table_name, column_name from $(DBASENAME).information_schema.columns
ORDER BY table_schema ASC
GO
lxxvi.	select table_schema, table_name, column_name from master.information_schema.columns
ORDER BY table_schema ASC
GO
lxxvii.	TableCol.sql
lxxviii.	TableColParam.sql



-- How to check all the commands
Sqlcmd -?


-- Run sqlcmd with error code:
sqlcmd -E -q “create table adventureworks” –b -S DESKTOP-5K4TURF\SQLEXPRESS



-- How to run inline query from sqlcmd
sqlcmd -S DEHMOBILE-PD\SQLEXPRESS -E -q "select name from sys.databases"


-- Working in SSMS in Sql Mode
b.	Basic Commands (DB/Query)
c.	How to run .sql script on sqlcmd



-- How to run query in script (output to file)
-- Write the Transact Sql script in .txt. file and save as .sql
sqlcmd -S DEHMOBILE-PD\SQLEXPRESS -E -i .\CreateDb.sql -o .\CreateDbot.log




-- How do you restore db from .bak file (write instructions)
-- You must have a .bak file
-- (rclick on Databases) + Restore database
--General Tab|Source Section -> Device
--(click) Browse “…”
--	(click) Add (wait for the dropdownlist)
--Select the .bak file and press OK
--Note: Its better to save the .bak file in C:

--How do you attach db from mdf file (write instructions)
--Load adventure works database
--DataDictionary: https://www.sqldatadictionary.com/AdventureWorks2014/
--Sample queries
--Load Northwind database

-- TO BACKUP A DB YOU DONT NEED TO CHANGE INTO A PARTICULAR CONTEXT. 

-- Non parametrix version.
-- Notes: Its better if target path is in C:\
-- Run with: 2.	a.	sqlcmd -S DEHMOBILE-PD\SQLEXPRESS -E -i .\BackupDb.sql -o .\BackupDblog.txt
BACKUP DATABASE [AdventureWorks2014] TO  DISK = N'C:\Newbackup2.bak'
GO

-- Parametric DBName version
-- Run with: sqlcmd -S DEHMOBILE-PD\SQLEXPRESS -E -i .\BackupDb.sql -v DBASENAME ="AdventureWorks2014" -o .\ListedTables.txt
BACKUP DATABASE $(DBASENAME) TO  DISK = N'C:\Newbackup2.bak'
GO

-- Full Parametric version (DBname, TargetPath)
-- Run with: sqlcmd -S DEHMOBILE-PD\SQLEXPRESS -E -i .\BackupDb.sql -v DBASENAME ="AdventureWorks2014" 
-- -v TARGETPATH = "ENTERFULLPATH" -o .\ListedTables.txt
BACKUP DATABASE $(DBASENAME) TO  DISK = $(TARGETPATH)
GO

-- sqlcmd -S DEHMOBILE-PD\SQLEXPRESS -E -i .\CreateDb.sql -0 .\CreateDbot.log
CREATE DATABASE NEWDBNAME;
GO

-- sqlcmd -E -v DATABASENAME=”Userinput” -i
-- sqlcmd -S DEHMOBILE-PD\SQLEXPRESS -E -v DATABASENAME=”Userinput” -i .\CreateDb.sql -0 .\CreateDbot.log
CREATE DATABASE $(DATABASENAME);
GO

-- sqlcmd -S DEHMOBILE-PD\SQLEXPRESS -E -i .\ListAllDb.sql -0 .\ListAllDb.log
SELECT name FROM sys.databases
GO

-- sqlcmd -S DEHMOBILE-PD\SQLEXPRESS -E -i .\SPListAllDb.sql -0 .\SPListAllDb.log
Sp_databases;
GO