ax4413

DECLARE @TableName varchar(100) = 'SomeTableName'
 
SELECT  'CREATE TABLE ' + QUOTENAME(SCHEMA_NAME()) + '.' + QUOTENAME(t.name) + CHAR(10) + '(' + CHAR(10)
	+ CHAR(9) + col.List + ') ON [' + ds.name + '];' AS SQLStatement
FROM sys.tables t
	CROSS APPLY(SELECT DISTINCT STUFF(
					(SELECT CHAR(9) + ', ' + QUOTENAME(c.name) + ' ' + QUOTENAME(ty.name) + 
						CASE ty.name 
							WHEN'char' THEN '(' + cast(c.max_length AS VARCHAR(5)) + ')'
							WHEN'date' THEN ''

ax4413

SELECT 'ALTER TABLE '+ QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + QUOTENAME(t.name)
	+ ' ADD  CONSTRAINT ' + QUOTENAME(i.name) 
	+ ' PRIMARY KEY CLUSTERED (' 
	+ pk.ColumnList + ')'
	, i.*
FROM sys.tables t
	INNER JOIN sys.indexes i on i.object_id = t.object_id
    CROSS APPLY (SELECT DISTINCT STUFF((SELECT ', ' +  QUOTENAME(c.name) + ' ASC '
				FROM sys.index_columns ic
					INNER JOIN sys.columns c ON c.column_id = ic.column_id AND c.object_id = ic.object_id

ax4413

SELECT 'UPDATE '+ QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + QUOTENAME(t.name) + CHAR(10)
	+ 'SET'
	+ pk.ColumnList + ';'
FROM sys.tables t
    CROSS APPLY (SELECT DISTINCT STUFF((SELECT CHAR(10) + CHAR(9) + ', ' +  QUOTENAME(c.name) + ' = ' + 'REPLACE(' + QUOTENAME(c.name) + ', ''"'', '''')'
				FROM sys.columns c 
				WHERE c.object_id = t.object_id
				FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)'),1,3,'') AS ColumnList
				)pk 
;

ax4413

#
# Working with branches
#

# Get the current branch name (not so useful in itself, but used in
# other aliases)
branch-name = "!git rev-parse --abbrev-ref HEAD"
# Push the current branch to the remote "origin", and set it to track
# the upstream branch
publish = "!git push -u origin $(git branch-name)"

ax4413

#
# Working with branches
#

# Get the current branch name (not so useful in itself, but used in
# other aliases)
branch-name = "!git rev-parse --abbrev-ref HEAD"
# Push the current branch to the remote "origin", and set it to track
# the upstream branch
publish = "!git push -u origin $(git branch-name)"

ax4413

Keybase proof

I hereby claim:

• I am ax4413 on github.
• I am stephenyeadon (https://keybase.io/stephenyeadon) on keybase.
• I have a public key whose fingerprint is 78E2 AF8A 0074 FA41 79ED AC17 0DCF 608F 157B D059

To claim this, I am signing this object:

ax4413

<!-- 1) Original command -->
<Exec Command="SQLCMD -S %22$(DBServerInstance)%22 -t -i %22$(SqlCmdScript)%22 -v Param1=%22Param1Value%22 Param2=%22Param2Value%22 Param3=%22Param3Value%22"/>
 
 
 
 <!-- 2) Concat items in a property group with white spaces -->
<ItemGroup>
  <Arg Include="-S %22$(192.168.0.1)%22" />
  <Arg Include="-t" />
  <Arg Include="-i %22$C:\Temp\Script.sql%22" />

ax4413

SELECT	'SELECT' + CHAR(9) + '[Table] = ' + QUOTENAME(t.name, CHAR(39)) + ', ' + CHAR(10) + CHAR(9) + CHAR(9) + Cols.List + CHAR(10) +
	'FROM' + CHAR(9) + @Left + '.' + QUOTENAME(t.name) + ' ' + CHAR(10) +
	'EXCEPT ' + CHAR(10) +
	'SELECT' + CHAR(9) + '[Table] = ' + QUOTENAME(t.name, CHAR(39)) + ', ' + CHAR(10) + CHAR(9) + CHAR(9) + Cols.List + CHAR(10) +
	'FROM' + CHAR(9) + @Right + '.' + QUOTENAME(t.name) + ' ' + CHAR(10)
FROM	sys.tables t
	CROSS APPLY (	SELECT	DISTINCT STUFF( (SELECT ', ' + CASE WHEN ty1.name = 'TEXT' THEN ' CAST( ' + QUOTENAME(c1.name) + ' AS VARCHAR(MAX))' ELSE QUOTENAME(c1.name) END
						FROM	sys.columns c1 
							INNER JOIN sys.types ty1
								    ON ty1.user_type_id = c1.user_type_id

ax4413

SELECT	'SELECT ' +
    	'CASE WHEN ' + Ord.Predicate + ' != ' + cast(t.max_column_id_used as varchar(50)) + ' THEN 1 ELSE 0 END AS [NoMatch], ' +				                -- identify where left and right columns do not join 
    	'* ' +																																					-- select *
    	'FROM ' + @Left +'.'+ QUOTENAME(t.name) + ' src ' +																										-- from T1
    	'FULL OUTER JOIN ' + @Right + '.' + QUOTENAME(t.name) + ' tgt ' +																						-- join T2
    	' ON COALESCE(tgt.' + QUOTENAME(c.name) + ', ' + tyd.Value + ') = COALESCE(src.' + QUOTENAME(c.name) + ', ' + TYD.Value + ') ' +			            -- If joining on a null use a default type value
    	Cols.Predicate + ' ' +																																	-- the rest of teh join syntax. If joining on a null use a default type value
    	'WHERE CASE WHEN ' + Ord.Predicate + ' != ' + cast(t.max_column_id_used as varchar(50)) + ' THEN 1 ELSE 0 END = 1 ' +					                -- exclude rows where there is a match
    	'ORDER BY CASE WHEN ' + Ord

ax4413

DECLARE @T TABLE(Name VARCHAR(128));
 
INSERT INTO @T 
VALUES  ('Table1'),
        ('Table2'),
        ('table3');
    
DECLARE @Type TABLE(Name VARCHAR(150), Value VARCHAR(50))
INSERT INTO @Type 
VALUES  ('bigint','1'),