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:
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 '' |
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 |
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 | |
; |
# | |
# 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)" |
# | |
# 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)" |
I hereby claim:
To claim this, I am signing this object:
<!-- 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" /> |
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 |
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 |
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'), |