Create SQL script for Tables in Database

select  'create table [' + so.name + '] (' + o.list + ')' + CASE WHEN tc.Constraint_Name IS NULL THEN '' ELSE 'ALTER TABLE ' + so.Name + ' ADD CONSTRAINT ' + tc.Constraint_Name  + ' PRIMARY KEY ' + ' (' + LEFT(j.List, Len(j.List)-1) + ')' END
from    sysobjects so
cross apply
    (SELECT 
        '  ['+column_name+'] ' + 
        data_type + case data_type
            when 'sql_variant' then ''
            when 'text' then ''
            when 'ntext' then ''
            when 'xml' then ''
            when 'decimal' then '(' + cast(numeric_precision as varchar) + ', ' + cast(numeric_scale as varchar) + ')'
            else coalesce('('+case when character_maximum_length = -1 then 'MAX' else cast(character_maximum_length as varchar) end +')','') end + ' ' +
        case when exists ( 
        select id from syscolumns
        where object_name(id)=so.name
        and name=column_name
        and columnproperty(id,name,'IsIdentity') = 1 
        ) then
        'IDENTITY(' + 
        cast(ident_seed(so.name) as varchar) + ',' + 
        cast(ident_incr(so.name) as varchar) + ')'
        else ''
        end + ' ' +
         (case when IS_NULLABLE = 'No' then 'NOT ' else '' end ) + 'NULL ' + 
          case when information_schema.columns.COLUMN_DEFAULT IS NOT NULL THEN 'DEFAULT '+ information_schema.columns.COLUMN_DEFAULT ELSE '' END + ', ' 

     from information_schema.columns where table_name = so.name
     order by ordinal_position
    FOR XML PATH('')) o (list)
left join
    information_schema.table_constraints tc
on  tc.Table_name       = so.Name
AND tc.Constraint_Type  = 'PRIMARY KEY'
cross apply
    (select '[' + Column_Name + '], '
     FROM   information_schema.key_column_usage kcu
     WHERE  kcu.Constraint_Name = tc.Constraint_Name
     ORDER BY
        ORDINAL_POSITION
     FOR XML PATH('')) j (list)
where   xtype = 'U'
AND name    NOT IN ('dtproperties')
Tagged with: ,
Posted in SQL Server

Create and apply Patch in git

Creating Patch in git is quite easy and useful when you want to transfer your changes from one branch to another or from one machine to another.

so here is the command you use:

once you clone repo from git using :

git clone git://github.com/xyx/xyx.git
cd xyx
git checkout -b branch_1

git checkout -b : it will create new branch with branch_1 name

creating patch

git log --pretty=oneline -3
git format-patch master --stdout > patch_name.patch

above command create new patch file with name patch_name.patch.

now applying patch:
use following command to easily apply patch to branch, apply command shows what are the changes patch will do in your current branch

git apply --check patch_name.patch

use following command to apply the patch

git am --signoff < patch_name.patch
Tagged with:
Posted in SQL Server

TSQL Cursor Syntext

declare cursor cust_cursor fast_forward for
select custid from tblCustomer
Open cust_cursor
fetch next from cust_cursor into @custId
while @@fetch_status = 0
begin
fetch next from cust_cursor into @custId
end
close cust_cursor
deallocate cust_cursor
Tagged with: ,
Posted in SQL Server

Table compression in SQL Server

If you are using SQL Server 2008 or SQL Server 2008R2, or 2012 you can use table compression in addition to the indexes to get more efficient storage.

Basically table compression has two levels:

1. Row :row-level compression, sql server applies compact storage format for each row in table
2. Page: page-level compression, sql server apply compression algorithms to get more efficient storage on page level.

here is syntax to apply table compression while creating table:

Create table [tablename] (column_name column_type) with (DATA_COMPRESSION = ROW).

To measure how efficient is row or page compression is, you can use sp_estimate_data_compression_savings stored procedure.

The stored procedure accepts table_name, index_id, partition_type and type of compression you would like to apply and return how much data space would be allocated with given compression.

Usually with page compression you can get more efficient storage rather than row based compression as SQL server use compression algorithm for storage.

Tagged with:
Posted in SQL Server

Difference between Date Datatypes in SQL Server 2012

2013-12-03_0311

What is basic difference between different date data types in sql server 2012. In SQL 2012, there are new date datatypes have been included such as Datetime2, DateTimeOffset, smalldatetime, Time, Date.

Major difference between each is date time precision and storage space. So, Let look by changing one date data type to other how much space we could save. And saving more space that also means when we index, retrieve or filter data using where condition it also have performance benefits.

The best way to find out how much memory being using by datatype is to create table with datatypes and check the column description using SP_helptext [table_name].


create table tblDate (dt1 datetime, dt2 datetime2,
t1 time, d1 date, dtoff datetimeoffset, sdt smalldatetime)

 

 

2013-12-03_0311

Select Table name and press Alt+F1 or use sp_helptext tblDate.

It’s clear from the data filed length that if you want to store just date then use DATE field and if your looking to store time then go for SMALLDATETIME, for little more sophesticated datetime then just follow the conventional DATETIME.

But if you are looking for precision then it’s advise to use DATETIME2(7) use it with precision so that you are not over burdening the server to store more precise values.

DATETIMEOFFSET is the most expensive date datatype in sql 2012, having said that it might be more appropriate to use when your application is dealing with multiple time zones.

I also found this link which gives you max date value supported by sql server.

check it out

Tagged with: , , ,
Posted in SQL Server

Gradient in CSS3

Check out my previous post about text-shadow ,  box-shadow  and Draw simple drawing board application using HTML5 Canvas.

This post is about how to use gradient background or gradient color without using any image which is purely using HTML5
CSS3 style sheet.

let’s look at the syntax:

background: linear-gradient([Direction] [color] [percentage]);

Direction: (Top/45 deg/Left/Radial) in which direction you need to spread color

Color: which color to fill

Percentage: what percentage of the areal to be filled with Color.

there are multiple syntax supported by different browser.

check out the following CSS3 syntax for different browser.

background: #b3dced; /* Old browsers */
/* IE9 SVG, needs conditional override of 'filter' to 'none' */
background: -moz-linear-gradient(-45deg, #b3dced 0%, #29b8e5 50%, #bce0ee 100%); /* FF3.6+ */

background: -webkit-gradient(linear, left top, right bottom, color-stop(0%,#b3dced), color-stop(50%,#29b8e5), color-stop(100%,#bce0ee)); /* Chrome,Safari4+ */
background: -webkit-linear-gradient(-45deg, #b3dced 0%,#29b8e5 50%,#bce0ee 100%); /* Chrome10+,Safari5.1+ */
background: -o-linear-gradient(-45deg, #b3dced 0%,#29b8e5 50%,#bce0ee 100%); /* Opera 11.10+ */
background: -ms-linear-gradient(-45deg, #b3dced 0%,#29b8e5 50%,#bce0ee 100%); /* IE10+ */
background: linear-gradient(135deg, #b3dced 0%,#29b8e5 50%,#bce0ee 100%); /* W3C */
filter: progid:DXImageTransform.Microsoft.gradient( startColorstr='#b3dced', endColorstr='#bce0ee',GradientType=1 ); /* IE6-8 fallback on horizontal gradient */

check out the following ultimage css3 gradient generators from others:

http://www.colorzilla.com/gradient-editor/

http://gradients.glrzad.com/

and one more

Tagged with: , ,
Posted in css, html

Box-Shadow using CSS3

CSS3 come with really interesting list of features for website designer, which allow them to make amazing website.
One of them is box-shadow.

let look at the syntext:

box-shadow: x-shadow y-shadow blur spread inset/outset(default);

as wordpress hosting does not allow me to use <Style> tag in post I have posted some interesting box-shadow examples on my personal blog at following link

http://blog.jigneshpatel.com.au/css3-box-shadow/

hope you like it, Looking forward to receive feedback from you. :)

Tagged with: ,
Posted in css
Follow

Get every new post delivered to your Inbox.

Join 38 other followers

%d bloggers like this: