Multiple columns into a pivot.

Putting multiple columns into a pivot.
Took some time to figure out, but I got it to work.

1. Make data types of all columns the same.
Description: The idea is to make all columns of the same data type (Base query)

2. Unpivot the columns so they have a key, column name, and column value.
Description: Put all columns into one column with the "Column name and value" into a "ColName" field AND put the columns value into another field called "ColValue" (unpvt).

3. Finally, Pivot the values out by the "ColName" column
Description: The key and the values that were pivoted is what you want to select on this last step.

Example:
,Base AS (
    SELECT theKey, Field3, Cast(Field1 as varchar) as Field1, Cast(Field2 as varchar) as Field2, cast(Field3 as varchar) as Field32
    FROM theData
)
,unpvt AS (
SELECT theKey, ColName + CONVERT(varchar, Field3) AS ColName, ColValue
FROM Base
UNPIVOT (ColValue FOR ColName IN (Field1, Field2, Field32)) AS pvt
)
,repvt as (
SELECT theKey, Field1P, Field2P, Field32P, Field1R, Field2R, Field32R, Field1S, Field2S, Field32S, Field1C, Field2C, Field32C
FROM unpvt
PIVOT (MIN(ColValue) FOR ColName IN (Field1P, Field2P, Field32P, Field1R, Field2R, Field32R, Field1S, ModIdS, Field32S, Field1C, Field2C, Field32C)) AS pvt
)

Opening Remote SQL Server through VPN

I wanted to be able to open sql server on my own machine and use the vpn as the connection. I had to create a shortcut on my desktop to login through the VPN.
1.Create shortcut on desktop and add the following extension:
C:\Windows\System32\runas.exe /netonly /user:remotedomain\remoteusername "C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"

2. Connect to VPN
3. Double click on ShortCut to open Sql Server.
NOTE: You will need to login with the remote password when you double click on the shortcut.
FYI: Also, I had to have the ip address on the box that sql server was being hosted on to make it work.
Hope this helps someone.

Here is a script that generates a script to check each column in a particular table whether they are completely NULL.

 A person would only need to take the UNION ALL off of the ending to run it with no problems.


Declare @NULL_COLUMNS TABLE(
table_name varchar(100),
[schema_name] varchar(130),
column_name varchar(150),
SelectStatement varchar(max)
)

Declare @Table_Name varchar(30)

set @Table_Name = 'TableName'

Insert Into @NULL_COLUMNS
Select
t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name,
'Select ''' + SCHEMA_NAME(schema_id) + '.' + t.name + '.' + c.name + ' - '' + Cast(isnull(AN.AmountNULL,0) as varchar(20)) + ''/'' +  Cast(count(1) as varchar(20)) + '' is NULL '' as [Column], isnull(AN.AmountNULL,0) as AmountNULL, count(1) as Total from '+ SCHEMA_NAME(schema_id) + '.'  + t.name + ' ai left join (Select ''' + c.name + ''' as ColumnName, count(1) as AmountNULL from '+ SCHEMA_NAME(schema_id) + '.' + t.name + ' where ' + c.name + ' is null ) AN on AN.ColumnName = ''' + c.name + ''' group by isnull(AN.AmountNULL,0) UNION ALL '  as SelectStatement
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name not in  ('CreatedBy','CreatedDate','LastModified','LastModifiedBy')
and c.is_identity <> 1
and t.name = @Table_Name
ORDER BY schema_name, table_name;


Select * from @NULL_COLUMNS