How to get list of all tables with Constraints in sql server

How to get list of all tables with Constraints in sql server?

There are different to get this information. Remember that all objects in a database is stored with an id in sys.objects table with its type.

If you do

Select *  FROM sys.objects, you can see name, object_ID, principal_ID, schema_ID, parent_object_ID, type and type_desc with other information.

Type generally give you what kind of an object it is.

So this list will give you all the object type.

 Select distinct type  FROM sys.objects;

So to find all the table with Constraints, we can write simple SQL like this.

SELECT OBJECT_NAME(parent_object_id), type, type_desc  FROM sys.objects WHERE type_desc LIKE '%cons%'

Another way to get same information is to use  Table_Constraints. This table hold information about only those tables which has constraints.

SELECT *
 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS;

Connecting from one server to another server using SQLCMD in Management Studio

In SQL Server Management Studio, most of the time, we have to change connection to different server to run same query to validate SQL Queries. To do so, we always use change connection feature by right clicking mouse and then changing to server where we want to run our queries.

There is another way to do so without going through all this using SQLCMD Command.

To do so add SQLCMD to your tool bar.

Then

Run this.

Select @@ServerName --> This give your current server Name
GO

:Connect ServerName --> This will change to your different serverName
Go

USE DatabaseName;
GO


Select GETUTCDATE()
Run your query here

That's it.

Implementing SSIS Package Using File System.

Depending on how you or your organizations want to implement SSIS packagesduring Development, Test or Production environment, Let's see how we can do file system implementation.  You can save your ssis package in either SQL Server Store or in a folder. I will be specially looking at File System implementation.

To do so, we will have to create following files first:
1. SSIS_Cmd.cmd
2. DB_cfg.dtsConfig
3. PackageName_ExeCfg.txt


So let's look at each of them:


SSIS_Cmd.cmd

This cmd file list where our sql server is installed, where our integration engine is running, our package directory,  where we are saving our log file. Our database config, and how to handle error. You have to create just one file for a each server.

So let's look at the file itself:
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
set BIN=C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn
set ROOTDIR=C:\SSIS

SetLocal EnableDelayedExpansion
set content=
for /F "delims=" %%i in (%1) do set %%i

set SSISPACKAGESDIR=%ROOTDIR%\%PACKAGEDIRNAME%\jobs\SSIS_Packages
set LOGDIR=%SSISPACKAGESDIR%\logs

"%BIN%\dtexec.exe" /FILE "%SSISPACKAGESDIR%\%SSISPACKAGE%" /CONFIGFILE "%SSISPACKAGESDIR%\%CONFIG_CUSTOM%" /CONFIGFILE "%ROOTDIR%\%CONFIG_DB%"  /CHECKPOINTING OFF /REPORTING EWCDI 1>"%LOGDIR%\%LOGFILE%"

IF %errorlevel% NEQ 0 GOTO DTSRUN_ERR_EXIT
GOTO SUCCESS_EXIT
:DTSRUN_ERR_EXIT
echo.
echo ABENDED: ERROR running SSIS package
exit /B %errorlevel%

GOTO EXIT
:SUCCESS_EXIT
echo.
echo COMPLETED SUCCESSFULLY

GOTO EXIT
:EXIT
echo.
echo FINISHED: %date% %time%
echo on

EndLocal

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Next is DB_ConFig.cmd file

DB_cfg.dtsConfig
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
<?xml version="1.0" encoding="utf-8"?>
<DTSConfiguration>
  <DTSConfigurationHeading>
    <DTSConfigurationFileInfo GeneratedBy="AMERICAS\straleyd" GeneratedFromPackageName="PackageName" GeneratedFromPackageID="{7DD1CDBA-6AD6-4D5D-84A0-4258BA92E894}" GeneratedDate="5/30/2014 10:01:10 AM" />
  </DTSConfigurationHeading>
  <Configuration ConfiguredType="Property" Path="\Package.Connections[Connection_Manager_Name].Properties[InitialCatalog]" ValueType="String">
    <ConfiguredValue>DatabaseName</ConfiguredValue>
  </Configuration>
  <Configuration ConfiguredType="Property" Path="\Package.Connections[Connection_Manager_Name].Properties[Password]" ValueType="String">
    <ConfiguredValue>Password!!!!!</ConfiguredValue>
  </Configuration>
  <Configuration ConfiguredType="Property" Path="\Package.Connections[Connection_Manager_Name].Properties[ServerName]" ValueType="String">
    <ConfiguredValue>ServerName</ConfiguredValue>
  </Configuration>
  <Configuration ConfiguredType="Property" Path="\Package.Connections[Connection_Manager_Name].Properties[UserName]" ValueType="String">
    <ConfiguredValue>USerLoginName!!!!</ConfiguredValue>
  </Configuration>
</DTSConfiguration>

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
PackageName_ExeCfg.txt

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SSISPACKAGE= SSIS_Package_Name.dtsx
PACKAGEDIRNAME=DevBox !!!!!!!!!!!Directory where you saving package file DevBox
CONFIG_CUSTOM=Config file of the package which you want to usecfg.dtsConfig
CONFIG_DB=DB_dev_Cfg.dtsConfig   !!! DB Config file
LOGFILE=Name of Log File_log.txt

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

To Implement.

Create all the necessary folders. For this I will take Development as folder Name

Inside Development Folder, I will create following folders:

archive -- if I have to archive any file from any of the package
inbound - folder to hold any files
Jobs- where I will save all my ssis package related files, and
outbound, if I have to write to any files.

Inside job folder, I will create two more folder

SSIS_EXEC and SSIS_Package
Inside SSIS_Package, I will save all the executable's related


For example say I create a dtsx file.

Example.dtsx - Package file
Example.dtsConfig --ConFig file
Example_Exe.txt - file with all the information.


Now go to sql server agent and create a job using  Operating System (CMDEXEC) and List the path  where you all the files are:

C:\SSIS\SSIS_Cmd.cmd   C:\SSIS\Develpoment\jobs\ssis_packages\Example_ExeCfg.txt

and execute it.

Sql Server: Stored Procedure to backup databases

Sql Server: Stored Procedure to backup databases

Here's an example of Stored Procedure which uses cursor to backup all your databases.

Go to Master database

USE MASTER
GO;

And then create this stored Procedure. Change your path to where you want to do the backup.

-- SP using cursor to back up database

Create Procedure sp_databases_backup

As
BEGIN
set nocount on
set xact_abort on



Declare @name varchar(128)  --database name
Declare @path varchar(256)  --Path to backup database file
Declare @filename varchar(128) -- name of database backup file
Declare @fileDate varchar(20) --Date when the database was backup




SET @path = 'C:\backup\'
SET @fileDate = convert(varchar(20), getdate(),112)

DECLARE db_cursor CURSOR FOR
Select name from dbo.sysdatabases
Where name NOT IN ('master', 'model', 'msdb', 'tempdb')

OPEN db_cursor
FETCH NEXT from db_cursor INTO @name

WHILE @@Fetch_status = 0
BEGIN
SET @filename = @path+@name+'_'+@fileDate+'.Bak'
BACKUP DATABASE @name TO DISK =@filename

FETCH NEXT FROM db_cursor INTO @name

END

CLOSE db_cursor

Deallocate db_cursor

END

SQL Server: Keeping track of changing data in a table

Sometime we are asked to keep a track of how data are changing over time in a table. This is critical in DW world. If they have (most of the time, they have Enterprise Edition so they can implement CDC method).

However there are some simple method we can use to keep track of data change for Insert/Update/Deleted.


Let's see how we can do this.

Steps 1. Let's create a table where we want to keep track of data change.



SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [UserTable](
ID [int] IDENTITY(1,1) NOT NULL,
FName varchar(20) NOT NULL,
LName varchar(20) NOT NULL,
Address1 varchar(40),
Address2 varchar(40),
City varchar(20) NOT NULL,
ZipCode varchar(10),
[StateCode] varchar(2) NOT NULL,
CreatedDate Datetime NOT NULL,
CreateBy int,
UpdatedDate Datetime NOT NULL,
UpdatedBy int



 CONSTRAINT [PK_UserTable] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

GO


Step 2.Let's create a archive table where we will keep track of all the data that is changing in above table.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [UserTable_Archive](
[ArchiveID] [bigint] IDENTITY(1,1) NOT NULL,
[ArchiveDate] [datetime] NOT NULL,
[ArchiveAction] [varchar](3) NOT NULL,
ID [int]  NOT NULL,
FName varchar(20) NOT NULL,
LName varchar(20) NOT NULL,
Address1 varchar(40),
Address2 varchar(40),
City varchar(20) NOT NULL,
ZipCode varchar(10),
[StateCode] varchar(2) NOT NULL,
CreatedDate Datetime NOT NULL,
CreateBy int,
UpdatedDate Datetime NOT NULL,
UpdatedBy int
 CONSTRAINT [PK_ArchiveID_Archive] PRIMARY KEY CLUSTERED
(
[ArchiveID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [UserTable_Archive] ADD  CONSTRAINT [DF_UserTable_ArchiveDate]  DEFAULT (getdate()) FOR [ArchiveDate]
GO



--Step 3. Let's write trigger for Update/Delete/Insert


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



create trigger UserTable_Archive_Delete
on UserTable for delete

as


insert UserTable_Archive (
 [ArchiveDate]
, [ArchiveAction]
, ID
, FName
, LName
, Address1
, Address2
, City
, ZipCode
, [StateCode]
, CreatedDate
, CreateBy
, UpdatedDate
, UpdatedBy
)
select
 getdate()
, 'DLT'
, ID
, FName
, LName
, Address1
, Address2
, City
, ZipCode
, [StateCode]
, CreatedDate
, CreateBy
, UpdatedDate
, UpdatedBy

from deleted

GO

create trigger UserTable_Archive_Insert
on UserTable for Insert

as


insert UserTable_Archive (
 [ArchiveDate]
, [ArchiveAction]
, ID
, FName
, LName
, Address1
, Address2
, City
, ZipCode
, [StateCode]
, CreatedDate
, CreateBy
, UpdatedDate
, UpdatedBy
)
select
 getdate()
, 'INSERT'
, ID
, FName
, LName
, Address1
, Address2
, City
, ZipCode
, [StateCode]
, CreatedDate
, CreateBy
, UpdatedDate
, UpdatedBy

from INSERTED

GO




create trigger UserTable_Archive_Update
on UserTable for Update

as


insert UserTable_Archive (
 [ArchiveDate]
, [ArchiveAction]
, ID
, FName
, LName
, Address1
, Address2
, City
, ZipCode
, [StateCode]
, CreatedDate
, CreateBy
, UpdatedDate
, UpdatedBy
)
select
 getdate()
, 'UPD'
, ID
, FName
, LName
, Address1
, Address2
, City
, ZipCode
, [StateCode]
, CreatedDate
, CreateBy
, UpdatedDate
, UpdatedBy

from Inserted

GO

Step 4:  Let's insert some data and see how it is implemented.

Insert into UserTable (FName ,LName,Address1 , Address2 , City , ZipCode , [StateCode], CreatedDate , CreateBy, UpdatedDate , UpdatedBy ) Values ('Mary','Stephon', '2250 Rock Road', '231', 'Dallas', '123456', 'TX', getdate() , 0, getdate(), 0)
Insert into UserTable (FName ,LName,Address1 , Address2 , City , ZipCode , [StateCode], CreatedDate , CreateBy, UpdatedDate , UpdatedBy ) Values ('Mary','Jane', '2251 Rock Road', '231', 'Dallas', '123456', 'TX', getdate() , 0, getdate(), 0)
Insert into UserTable (FName ,LName,Address1 , Address2 , City , ZipCode , [StateCode], CreatedDate , CreateBy, UpdatedDate , UpdatedBy ) Values ('Mary','Simon', '2252 Rock Road', '231', 'Dallas', '123456', 'TX', getdate() , 0, getdate(), 0)
Insert into UserTable (FName ,LName,Address1 , Address2 , City , ZipCode , [StateCode], CreatedDate , CreateBy, UpdatedDate , UpdatedBy )Values ('Mary','Colbert', '2253 Rock Road', '231', 'Dallas', '123456', 'TX', getdate() , 0, getdate(), 0)


Select * FROM UserTable;
Select * from UserTable_Archive;

Similary Let's do some update/Delete to existing data


Update UserTable
Set FName = 'Lisa'
Where LName = 'Jane'

Delete UserTable
where LName = 'Colbert'

And then let's see how data looks

Select * FROM UserTable;

Select * from UserTable_Archive;

There you go. However there is some cost associated with this method.

SQL To check for a day of a month

Suppose,  You are asked to design a SSIS package which should run on every 'Friday' of the week except for Friday which falls on 1st day of the month. For example November 1, 2013. This day is Friday and first day of the month.  So in our case this package should not run.


Assumption: You have a table called Calendartable with calendarDate, calendarYearMonth column
Sample data should look like

20140101, 201401
20140102, 201401


Let's first write T-SQL code to find out if it Friday and FirstDay of the month.

declare 
 @RunDate date
, @RunDate_YearMonth varchar(6)
, @ProcessingDateForThisMonth date
, @ProcessPackage int = 0

set @RunDate = '11/01/2013'

select @RunDate_YearMonth = CalendarDate from Calendartable where CalendarDate = @RunDate 

Print @RunDate_YearMonth;


select @ProcessingDateForThisMonth = min(CalendarDate) from Calendartable
where CalendarYearMonthCode = @RunDate_YearMonth --same month as run date
and datename(dw, CalendarDate) = 'Friday'  --day is a Friday
and day(CalendarDate) > 1 --not the 1st day of the month

Print @ProcessingDateForThisMonth

if @RunDate = @ProcessingDateForThisMonth
begin
set @ProcessPackage = 1
end
else
begin
set @ProcessPackage = 0
end

print @ProcessPackage

This sql set the value of ProcessPackage to 0 or 1 depending upon the date and datename. It will set to value of 0 only when the day happen to be firstday of the month and its friday otherwise it will be always 0.

Put Execute SQL Task in your package and take the value as your output value.


Declare @Now Date, @dayofmonth int=0, @ProcessPackage int = 0
Set @Now = getdate()
Set @dayofmonth = day(@now)

IF (@dayofmonth = 1 and datename(dw,@now) = 'Friday')
Set @ProcessPackage = 0
ELSE
SET @ProcessPackage = 1
Select @ProcessPackage

At this point, you should be ready to execute rest of your package based on this value

SQL Server: Converting Integer to Binary data

Let's first a create table which will be use to demonstrate conversion of integer to binary values. To keep this thing simple, let's keep our table as small as possible.

Create table NumberTable
(NumberID int IDENTITY Primary Key NOT NULL
,Number int
,Number_Name varchar(100)
)

--Let's insert some data in this table
Insert into  NumberTable
 VALUES (1, 'One'), (2, 'Two'), (3, 'Three'), (4, 'Four'), (5, 'Five')

-- Let's see what we have in this table.
Select * from Number;



--Let's convert this number to binary value. To do so, we will use Common Table Expression

With BinaryConversation AS
(Select Number, Number as WorkingNumber, cast('' as varchar(max)) as binary_values from NumberTable
-- Now we will do Union ALL with converted values from Common table Expression
UNION ALL
Select B.Number, B.WorkingNumber/2, cast(B.WorkingNumber%2 as varchar(max))+B.binary_values
FROM BinaryConversation B
Where WorkingNumber > 0) --This condition keep our results set to only converted values)
Select Number, binary_values from BinaryConversation Where WorkingNumber = 0
Order BY Number
;

Thanks to all those bloggers from whom I learn.

Here's a good resource I found on Binary and Decimal.