Generate Create statements for Statstics in SQL Server


 

Howdy EveryOne

if you want to if exists delete and create scripts for the statstics in the SQL Server, the below code works fine.

declare @SQ VARCHAR(4)
SET @SQ = ''''
;WITH StatNames
AS
(
SELECT  s.name StatName, T.name as TableName,s.stats_id stats_id, T.object_id [object_id]
FROM    sys.stats   S   WITH (NOLOCK)
JOIN    sys.Tables  T   WITH (NOLOCK)
ON      S.object_id     = T.object_id
AND     S.user_created  = 1
),
Deflat
AS
(
SELECT SN.StatName,SN.TableName,STUFF(
(  select  ',' + C.name
FROM    sys.stats_columns SC   WITH (NOLOCK)
JOIN    sys.columns C WITH (NOLOCK)
ON      SC.column_id = C.column_id
AND     C.object_id = SN.object_id
AND     SC.stats_id = SN.stats_id
AND     C.object_id = SC.object_id
ORDER   BY SC.stats_column_id
FOR     XML PATH ('')
),1,1,'') Cols
FROM  StatNames SN
)
SELECT 'IF EXISTS ( SELECT 1 FROM sys.stats S, sys.tables T WHERE S.name = ' + @SQ +  StatName + @SQ + ' AND T.name = ' + @SQ + TableName + @SQ + ' ) '
+ ' DROP STATISTICS ' + TableName +'.'+StatName
+ ' CREATE  STATISTICS ' + StatName  + ' ON ' + TableName + '(' + Cols + ')'
FROM Deflat

Generate SQL Insert Statements with Indentation


Howdy Everyone.

Generating SQL insert statements for a given table is always a tedious task in SQL Server, Mostly the automated generated scripts are not indented with spaces, so that it will be easier to read and most of all it will be easy to change a column values or remove column values.

The below code will give you the insert statements along with the indentation of the code.

 

<pre>IF EXISTS ( SELECT 1 FROM sys.procedures WHERE name = 'InsertGenerator')
    DROP PROCEDURE InsertGenerator

GO

CREATE PROCEDURE InsertGenerator
(
    @TableName  VARCHAR(100)
)
AS
BEGIN
    DECLARE @IndentationTable   TABLE
    (
        ColumnName      VARCHAR(500)
        ,MaxLength      BIGINT
    )

    DECLARE @SQL        NVARCHAR(MAX)
    DECLARE @Insert     NVARCHAR(MAX)
    DECLARE @SQ         VARCHAR(4)
    DECLARE @SSQ        VARCHAR(4)
    DECLARE @ColumnName VARCHAR(300)
    DECLARE @DataType   VARCHAR(50)
    DECLARE @MaxLength  BIGINT

    SET @SQ     = ''''
    SET @SSQ    = ''''''''''

    BEGIN TRY
        
        DECLARE maxLength CURSOR
        FOR
            SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WITH (NOLOCK) WHERE TABLE_NAME=@TableName 

        DECLARE Cols CURSOR
        FOR
            SELECT COLUMN_NAME,DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WITH (NOLOCK) WHERE TABLE_NAME=@TableName 


        OPEN maxLength
        FETCH NEXT FROM maxLength INTO @ColumnName
        WHILE (@@FETCH_STATUS = 0)
        BEGIN
            SET @SQL = 'SELECT ' + @SQ +  @ColumnName + @SQ + ' ColumnName, MAX(LEN('+@ColumnName+')) MaxLength FROM '  + @TableName  + ' '
            INSERT INTO @IndentationTable (ColumnName,MaxLength)
            EXEC sp_executeSQL @SQL
            FETCH NEXT FROM maxLength INTO @ColumnName
        END
        CLOSE maxLength
        DEALLOCATE maxLength

        UPDATE @IndentationTable         
        SET MaxLength = MaxLength + 8

        SELECT @Insert =  'SELECT ' + @SQ + 'INSERT INTO ' + @TableName + '(' + (SELECT STUFF( (SELECT ',' + ColumnName FROM @IndentationTable FOR XML PATH ('')),1,1,'')) + ' ) VALUES ( ' + @SQ + '+'
        SET @SQL = @Insert
        OPEN Cols
        FETCH NEXT FROM Cols INTO @ColumnName,@DataType
        WHILE (@@FETCH_STATUS = 0)
        BEGIN


            SELECT  @MaxLength = MaxLength 
            FROM    @IndentationTable
            WHERE   ColumnName = @ColumnName
            
            
                        
            SELECT @SQL = @SQL + CASE  
                                    WHEN @DataType IN ('BIGINT','BIT','DECIMAL','INT','MONEY','NUMERIC','SMALLINT','SMALLMONEY','TINYINT','FLOAT','REAL')   THEN   ' CAST( ' + @ColumnName + ' AS VARCHAR) '
                                    WHEN @DataType IN ('DATE','DATETIME2','DATETIME','DATETIMEOFFSET','SMALLDATETIME','TIME') THEN @SQ + 'CAST( ' + @SQ + '+' + @SSQ + '+' + 'CAST( ' + @ColumnName + ' AS VARCHAR ) ' + '+' + @SSQ + '+' + @SQ + ' AS ' + @DataType + ' ) ' + @SQ
                                    WHEN @DataType IN ('CHAR','VARCHAR','TEXT') THEN @SSQ + '+' + @ColumnName + '+' + @SSQ
                                    WHEN @DataType IN ('NCHAR','NVARCHAR','NTEXT') THEN 'N' + @SSQ + '+' + @ColumnName + '+' + @SSQ
                                    ELSE   CAST(@ColumnName AS NVARCHAR)
                                  END  +  '+' + 'REPLICATE( ' + @SQ+ ' ' +@SQ + ','+CAST(ISNULL(@MaxLength,20) AS VARCHAR) + '-LEN(' + @ColumnName + '))' +     '+'  + @SQ +',' + @SQ + '+'

            FETCH NEXT FROM Cols INTO @ColumnName,@DataType    
        END
        CLOSE Cols
        DEALLOCATE Cols

        SET @SQL = LEFT(@SQL,LEN(@SQL)-LEN('+'  + @SQ +',' + @SQ +  '+'))
        SELECT @SQL = @SQL + '+' + @SQ + ')' + @SQ +  ' FROM ' +  @TableName
        select @SQL 
        EXEC sp_executeSQL @SQL

    END TRY
    BEGIN CATCH
        SELECT CAST(ERROR_LINE() AS VARCHAR) + '    ' + ERROR_MESSAGE() + '   AT ' + CAST(ERROR_LINE() AS varchar)
    END CATCH
END

GO

To execute this code you have to give the stored proc name followed by the table name.

Example:
EXEC InsertGenerator <table_name>

Hope this helps you guys in generating a better and readable code.

Loop in SQL Server without For,while, goto


Hi Every one

Usually we all use a While, for or some old timers use GOTO to loop a batch. but there is some other way to do the same.


create table emp
(
id  INT IDENTITY(1,1) PRIMARY KEY
,name varchar(200) NULL
)
GO

insert into emp (name) values ('suri')
GO 100

&nbsp;

The above insert statement will insert 100 records into the table emp even though we have not mentioned any loop, The trick is to mention the number after the GO statement. It will loop through the complete batch (last GO statement to the current statement or from the start of the script).

Drawbacks: This can cause a serious trouble if we dont use the batch delimiter before the statement we want to execute, for example if we are missing the first GO statement we will get an error saying the table emp already exists, as it will try to execute the create table statement as well.

Get the object name from the fully qualified name


Howdy Everyone.

I have seen an interesting built in function of SQL Server today, It is PARSENAME.

The PARSENAME function will return the object/schema/database/server name from the given string.

For more information you can check MSDN article below

http://msdn.microsoft.com/en-us/library/ms188006.aspx

TSQL – How to find last sunday of the month


Howdy Every One.

How to get last day of any month is explained in my previous post..

Now, How to get the last sunday of any month… A little tricky isn’t it…. one of my friend went to write the loop and then tried to compare the values if it is a sunday or not from the last day of the month (by decreasing each day until he reaches a sunday)…. The method works but there is another way to do this… much simpler way….

To accomplish we need to know what day of week it is, we can get that by using dw parameter in the datepart.

Now if we know what day of the week the current input is… if the @@datefirst value is set to its default (7) then sunday will be 1 in the day of weeks.

If you subtract the number of days from seven and add it will give you the number of days between a sunday and the current date…….

Check the below code

declare @last_day_of_the_month	datetime
declare @last_sunday_of_the_month datetime
declare @input_date	datetime
set DATEFIRST 7 -- to be on the safe side
select @input_date = getdate()
select @last_day_of_the_month = dateadd(dd,datepart(dd,@input_date)*-1,dateadd(mm,1,@input_date))
select @last_sunday_of_the_month = dateadd(dd,(datepart(dw,@last_day_of_the_month)*-1)+1,@last_day_of_the_month)
select @last_sunday_of_the_month

How to find the last day of the month


Howdy Everyone.

Most of the times I see, people getting up with new ideas to get the last day of the month…

But the simplest method we can use in SQL Server is by adding 1 to month and subtracting the number of days in the date, confusing….

Well let me explain with an example

Let us take the date 20-AUG-2011. Now if we add 1 to the month by using DATEADD function.. the result will be 20-SEP-2011. Now the day part of the resultant date is 20 and if we subtract the 20 from the resultant by using dateadd the result will be 30-AUG-2011.

The code is given below

DECLARE @input_date datetime
set @input_date = getdate() -- for testing purposes
select dateadd(dd,datepart(dd,@input_date)*-1,dateadd(mm,1,@input_date))

Generate a Sequence number for each row in a table.


Howdy EveryOne

Generating a sequence a number for each and every row in a table…. Hmm if you are familiar with TSQL already I think you are thinking of creating Identity column like me…. (ofcourse if you are not an advanced user)…..

Now you can create the Identity column in a temporary table followed by the same schema of your own table to generate a sequence number for all the rows in your table, But in order to accomplish this task we are using a temporary table in between. If the data too small we might not see any performance issue but if the data is too large (too many record sets) then you are in serious trouble of performance.

How can we accomplish this task without the temp table in between… Is it possible, Yes it is ?

And again Thanks to Kent W. on MSDN forums who shown the best approach to do this….

DECLARE @a table
(
	a int
)
insert into @a values (1)
insert into @a values (1)
insert into @a values (1)
insert into @a values (1)
insert into @a values (1)
insert into @a values (1)
insert into @a values (1)
insert into @a values (1)
insert into @a values (1)
insert into @a values (1)
select row_number() over(order by (select 1)) , a from @a

Concatenate rows into a column separated by a delimiter


Howdy EveryOne

While working on the MSDN Forums for quite a while, I see every now and then  people wants to know how to concatenate a single column value of multiple rows into a single column…. I think it might be better understood by a example

Table A: INPUT

Col1
Row 1 a
Row 2 b
Row 3 c
Row 4 d

Desired output:

a,b,c,d

If this is the case then without using the XML tags previously we used to do the below one

declare @t table
(
	a char
)
insert into @t values ('a')
insert into @t values ('b')
insert into @t values ('c')
insert into @t values ('d')
declare @concate varchar(100)
set @concate = ''
SELECT @concate = @concate +','+ a
FROM @t
select STUFF(@concate,1,1,'')

Although it is a good approach, The select statement (concate = concate+…) needs to be executed in a separate batch. This approach will need an extra lines of code incase if this concatenated value is needed else where….

So, in search of a better solution, I stumbled upon a post written by Kent W. in MSDN forums with FOR XML PATH which will accomplish my task very smoothly.

Below is an example of how to write the above code with the FOR XML PATH

declare @t table
(
	a char
)
insert into @t values ('a')
insert into @t values ('b')
insert into @t values ('c')
insert into @t values ('d')
SELECT STUFF( (SELECT ',' + a FROM @t FOR XML PATH ('')),1,1,'')

Advantages of the second approach:
No need of additional variables.
Can be included in a subquery directly.