How to reseed a table via TSQL in SQL Server

Sometimes it's useful to restart the identity of a table back to a certain number. DBCC CHECKIDENT ([TableName],reseed,0)

How to get the identity of a row that was last inserted into a specific table in a SQL Server database.

Use the

IDENT_CURRENT

key word.
Here is the syntax: IDENT_CURRENT('stringTableName')

It could be used like this:

Declare @TestId int
set @TestId = IDENT_CURRENT('TestTable')

or it can be simply selected:
Select IDENT_CURRENT('TestTable')

Here is a link to Microsofts definition: IDENT_CURRENT

Setting up SSIS package to run automated.

There may be a time when a load of data from another server of updating may need to be done automated via SQL server. If an SSIS package is needed for this type of a situation. A job on SQL server can be created and inside one of the steps of that job is where the SSIS would be put as shown below.

Just found out how to generate insert statements in SQL Server Management Studio 2008. I had done it before, but this will be as a reminder.

1. Right click on the database and go to Tasks -> Generate Scripts
2. Select the tables (or object) that you want to generate the script against.
3. Go to Set scripting options and click on the Advanced button.
4. In the General category, go to Type of data to script
5. There are 3 options: Schema Only, Data Only, and Schema and Data. Select the appropriate option and click on OK.

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