-August 2019+
SMTWTFS
28293031123
45678910
11121314151617
18192021222324
25262728293031
1234567
  • RSS
  • Add To My MSN
  • Add To Windows Live
  • Add To My Yahoo
  • Add To Google

Statistics

  • Entries (4)
  • Comments (0)

script for Constraints and Indexes 

Sunday, July 10, 2011 1:03:00 PM

This script will help DBA's in scripting out all the existing
indexes(Clustered , Non-Clustered, Clustered with PK,
Clustered with Unique key, Non-Clustered with Unique Key etc.)
in a Database.Script will script the Partition Indexes which
exists on Partition Scheme,Primary Filegroup or on any Filegroup
In SQL Server 2008 there is no way to script a index with
Filegroup or Partition Scheme Name.
It seems to me as a BUG
as In SQL Server 2005 we can script the Index with Filegroup
and Partition Scheme Name.
Hope it will help lot of DBA's.
--------------------------------------------------------------
/*
Code Developed By      : Mohd Sufian
Code Developed Date    : 14th Novermber 2009
Code Developed Country : India
*/
SET NOCOUNT ON
---------------------------------------------------------------
     DECLARE @vNumDBs    Int 
      DECLARE @vCount     Int
--Decalared Variable Index Info-----------------------
      DECLARE @SchemaName           Varchar(MAX)
      DECLARE @TableName            Varchar(MAX)
      DECLARE @IndexName            Varchar(MAX)
      DECLARE @IndexType            Varchar(MAX)
      DECLARE @Index_Id             Varchar(MAX)
      DECLARE @Is_Primary_Key       INT
      DECLARE @Is_Unique_Key        INT
      DECLARE @Data_Space_id        INT
--Declared Variable Included Column In Index----------
      DECLARE @ColName              VARCHAR(max)
      DECLARE @Index_Column_id      INT
      DECLARE @KeyOrdinalid         INT
      DECLARE @partition_ordinal    INT
      DECLARE @IsDescendingKey      INT
      DECLARE @ColIncludedInPartitionFucntion VARCHAR(MAX)
-------------------------------------------------------
--Declare Storage Variable-----------------------------
      DECLARE @Rowcount INT
      DECLARE @Storage  INT
      DECLARE @IndexonFileGroup VARCHAR(MAX)
-------------------------------------------------------
--Declare Misleneous variables-------------------------
      DECLARE @CommaSeprator VARCHAR(1)
-------------------------------------------------------
DECLARE @Object_Holder TABLE (TabID int IDENTITY(1,1) ,
TableName varchar(max),Schemaname varchar(max))
INSERT INTO @Object_Holder(TableName,Schemaname)
SELECT sys.objects.NAME AS TABLENAME,
SCHEMA_NAME(sys.objects.SCHEMA_ID) AS SCHEMANAME
from sys.objects
INNER JOIN   sys.indexes ON
sys.objects.object_id = sys.indexes.object_id
and sys.indexes.type_desc!='HEAP'
GROUP BY sys.objects.name,
SCHEMA_NAME(sys.objects.SCHEMA_ID),sys.objects.type
HAVING (sys.objects.type='U') and sys.objects.name<>'sysdiagrams'
order by sys.objects.name --and sys.objects.name='Test1'
SET @vNumDBs = @@RowCount 
SET @vCount = 1
While @vCount <= @vNumDBs 
BEGIN
SELECT @SchemaName=Schemaname,@TableName=TableName
FROM @Object_Holder where TabID=@vCount
      ---Check for Indexes on Each Objects
      DECLARE @vNumIndex            Int 
      DECLARE @vCountIndex          Int

      Print '--Index Script for Object :::::'+@TableName
      CREATE Table #Index_Info_Holder (Index_RowID INT IDENTITY(1,1),
      Index_Name varchar(MAX),Index_Type varchar(MAX),Index_Id Int,
      ObjectID INT,IsPrimaryKey INT,IsUnique INT,data_space_id INT)
      INSERT INTO #Index_Info_Holder (Index_Name,Index_Type,Index_Id,
      ObjectID,IsPrimaryKey,IsUnique,data_space_id)
      SELECT name , type_desc ,index_id,object_id,is_primary_key,
      is_unique,data_space_id FROM sys.indexes where
      object_id=OBJECT_ID(@TableName) and type_desc!='HEAP'
      --'CDS_BreakMaster')--(@TableName)
      SET @vNumIndex = @@RowCount 
      SET @vCountIndex = 1
            WHILE  @vCountIndex <= @vNumIndex
      BEGIN
      SELECT @IndexName=Index_name ,@IndexType= Index_type ,
      @Index_Id=index_id,@Is_Primary_Key=IsPrimaryKey,
      @Is_Unique_Key=IsUnique,@Data_Space_id=data_space_id
      FROM #Index_Info_Holder
      where objectid=OBJECT_ID(@TableName)
      and Index_RowID=@vCountIndex
      If @IndexType='CLUSTERED' and  @Is_Primary_Key=1 --OR
      @IndexType='NON CLUSTERED'  or
      BEGIN
      Print 'ALTER TABLE ' + '[' + @SchemaName + '].[' + @TableName +'] ' +
                         'ADD CONSTRAINT ['+@IndexName+']' +' PRIMARY KEY CLUSTERED '
      Print '('
      END
      If @IndexType='NONCLUSTERED' and  @Is_Unique_Key=1--OR @IndexType='NON CLUSTERED'  or
      BEGIN
      Print 'ALTER TABLE ' + '[' + @SchemaName + '].[' + @TableName +'] '
                           + 'ADD CONSTRAINT ['+@IndexName+']' +' UNIQUE NONCLUSTERED '
      Print '('
      END
      If @IndexType='NONCLUSTERED' and  @Is_Unique_Key=0 and @Is_Primary_Key=0
      BEGIN
      Print 'CREATE NONCLUSTERED INDEX ['+@IndexName+'] ON' + ' [' + @SchemaName + '].['
                                         + @TableName  + ']'
      Print '('
      END
      If @IndexType='CLUSTERED' and  @Is_Unique_Key=0 and @Is_Primary_Key=0
      BEGIN
      Print 'CREATE CLUSTERED INDEX ['+ @IndexName +'] ON' + ' [' + @SchemaName + '].['
                                      + @TableName + ']'
      Print '('
      END
      ---Columns Included in Index--
      DECLARE @vNumIndexIncludedCol       Int 
      DECLARE @vCountIndexIncludedCol     Int
      CREATE TABLE  #Index_IncludedColumnInfo
      (
      Index_IncludedColumnRowID                   INT IDENTITY(1,1),
      Index_IncludedObjectId                      INT,
      Index_IncludedColName                       Varchar(MAX),
      Index_IncludedColID                         INT,
      Index_IncludedColKeyOrdinal                 INT,
      Index_IncludedColPartitionOrdinal           INT,
      Index_IncludedColPartitionIsDescendingKey   INT
      )   
      INSERT INTO #Index_IncludedColumnInfo
      (Index_IncludedObjectId,Index_IncludedColName,Index_IncludedColID,
       Index_IncludedColKeyOrdinal,Index_IncludedColPartitionOrdinal,
       Index_IncludedColPartitionIsDescendingKey)SELECT object_id,
       COL_NAME(object_id(@TableName),column_id),index_column_id,
       key_ordinal,partition_ordinal,is_descending_key FROM
       sys.index_columns where Object_Id=object_id(@TableName)|
      and index_id=@Index_Id and key_ordinal<>0--and Partition_ordinal!=1  
      SET @vNumIndexIncludedCol = @@RowCount 
      SET @vCountIndexIncludedCol = 1
      WHILE  @vCountIndexIncludedCol  <= @vNumIndexIncludedCol
      BEGIN
      SELECT @ColName=Index_IncludedColName,@Index_Column_id=Index_IncludedColID,
      @KeyOrdinalid=Index_IncludedColKeyOrdinal,
      @partition_ordinal=Index_IncludedColPartitionOrdinal,
      @IsDescendingKey=Index_IncludedColPartitionIsDescendingKey

      FROM #Index_IncludedColumnInfo WHERE
      Index_IncludedColumnRowID=@vCountIndexIncludedCol
      and Index_IncludedColKeyOrdinal<>0
            If @vCountIndexIncludedCol=@vNumIndexIncludedCol
            --or @vCountIndexIncludedCol  != @vNumIndexIncludedCol
                  BEGIN
                  SELECT @CommaSeprator=' '
            END
            If @vCountIndexIncludedCol<>@vNumIndexIncludedCol
            --and @vCountIndexIncludedCol  != @vCountIndexIncludedCol
                  BEGIN
                        SELECT @CommaSeprator=','
                  END 
      If @IsDescendingKey=0
            BEGIN
            Print '['+@ColName+'] ASC' + @CommaSeprator
            END
      If @IsDescendingKey=1
            BEGIN
            Print '['+@ColName+'] DESC'
            END
      SET @ColName=''
      SET @vCountIndexIncludedCol = @vCountIndexIncludedCol + 1
      END
      SELECT @ColIncludedInPartitionFucntion=COL_NAME(object_id(@TableName),column_id)
      FROM sys.index_columns where Object_Id=object_id(@TableName) and index_id=@Index_Id
      and Partition_ordinal=1   
      SELECT @Storage= Index_IncludedColPartitionOrdinal from #Index_IncludedColumnInfo
      where Index_IncludedColPartitionOrdinal>0
      Print ')'
      If @IndexType='CLUSTERED' and  @Is_Primary_Key=0 and  @Is_Primary_Key=0
      BEGIN
      Print 'WITH (PAD_INDEX  = ON, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF,
          IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON,
          ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80)'
    END
      If @IndexType='CLUSTERED' and  @Is_Primary_Key=1
      BEGIN
      Print 'WITH (PAD_INDEX  = ON, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF,
          IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON,
          ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80)'
    END
    If @IndexType='NONCLUSTERED' and  @Is_Unique_Key=1--OR @IndexType='NON CLUSTERED'  or
      BEGIN
      Print 'WITH (PAD_INDEX  = ON, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF,
          IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON,
          ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80)'
    END
    If @IndexType='NONCLUSTERED' and  @Is_Unique_Key=0 and @Is_Primary_Key=0
    BEGIN
    PRINT 'WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = ON, SORT_IN_TEMPDB = OFF,
               IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON,
               ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 70)'
      END
      SELECT @IndexonFileGroup=[name] FROM SYS.DATA_SPACES WHERE data_space_id=@Data_Space_id
    If @ColIncludedInPartitionFucntion IS NULL
         BEGIN
         SET @ColIncludedInPartitionFucntion=' '
         END
       If @ColIncludedInPartitionFucntion =''--IS NOT NULL
       BEGIN
       PRINT  'ON '+'['+@IndexonFileGroup+']'
        --+ '(['+@ColIncludedInPartitionFucntion+'])'
       END
       If @ColIncludedInPartitionFucntion !=''
          and @IndexonFileGroup<>'Primary'-- is not null --IS NOT NULL
       BEGIN
       PRINT  'ON '+'['+@IndexonFileGroup+']'+ '(['+@ColIncludedInPartitionFucntion+'])'
       END
       If @ColIncludedInPartitionFucntion !=''
          and @IndexonFileGroup='Primary'-- is not null --IS NOT NULL
       BEGIN
       PRINT  'ON '+'['+@IndexonFileGroup+']'--+ '(['+@ColIncludedInPartitionFucntion+'])'
       END
      SET @Storage=''
      DROP TABLE #Index_IncludedColumnInfo
      Print '---------End of Index Script------------------------'
      SET @vCountIndex = @vCountIndex + 1     
      END
      DROP TABLE #Index_Info_Holder
      --**********************************--
 SET @vCount = @vCount + 1 
END
SET NOCOUNT OFF
Note : The script will  Generate the create script for Constraints and Indexes.
 

Pivot in dynamic column 

Friday, July 1, 2011 10:49:00 AM


 

Pivots in SQL Server 2005 can rotate a table, i.e. they can turn rows into columns. PIVOTs are frequently used in reports, and they are reasonably simple to work with. However, I've seen quite a few questions about this operator. Most questions were about the column list in the PIVOT statement. This list is fixed, but many times the new columns are determined by the report at a later stage. This problem is easily solved when we mix pivots with dynamic SQL, so here is a very simple example about how to dynamically generate the pivot statement:

PIVOT allows you to turn data rows into columns. For example, if you have a table like this (I use only three months here for simplicity):

 

CREATE TABLE Sales ([Month] VARCHAR(20) ,SaleAmount INT)

INSERT INTO Sales VALUES ('January', 100)
INSERT INTO Sales VALUES ('February', 200)
INSERT INTO Sales VALUES ('March', 300)

SELECT * FROM SALES
 

Month             SaleAmount
----------------  -----------
January           100
February          200
March             300 

 

Suppose we wanted to convert the above into this:

 
January     February    March
----------- ----------  ----------
100         200         300

We can do this using the PIVOT operator, as follows:

SELECT  [January]
      , [February]
      , [March]
FROM    ( SELECT    [Month]
                  , SaleAmount
          FROM      Sales
        ) p PIVOT ( SUM(SaleAmount)
                    FOR [Month] 
                      IN ([January],[February],[March])
                  ) AS pvt

However, in the above example, I have the column names fixed as the first three months. If I want to create a result in which the columns are dynamic (for example, they are read from a table), then I need to construct the above query dynamically. To demonstrate this let’s look at the following example:

In the first table I have the column names I want to use:

CREATE TABLE Table1 (ColId INT,ColName VARCHAR(10))
INSERT INTO Table1 VALUES(1, 'Country')
INSERT INTO Table1 VALUES(2, 'Month')
INSERT INTO Table1 VALUES(3, 'Day')

In the second table I have the data. This consists of a row identifier (tID), a column ID (ColID) that refers to the column type in Table1, and a value:

CREATE TABLE Table2 (tID INT,ColID INT,Txt VARCHAR(10))

INSERT INTO Table2 VALUES (1,1, 'US')
INSERT INTO Table2 VALUES (1,2, 'July')
INSERT INTO Table2 VALUES (1,3, '4')
INSERT INTO Table2 VALUES (2,1, 'US')
INSERT INTO Table2 VALUES (2,2, 'Sep')
INSERT INTO Table2 VALUES (2,3, '11')
INSERT INTO Table2 VALUES (3,1, 'US')
INSERT INTO Table2 VALUES (3,2, 'Dec')
INSERT INTO Table2 VALUES (3,3, '25')

Now I would like to retrieve data from these two tables, in the following format:

tID         Country    Day        Month
----------- ---------- ---------- ----------
1           US         4          July
2           US         11         Sep
3           US         25         Dec 

In other words I want to turn the data rows in Table2 into columns. If I had a fixed set of columns for the result, i.e. the columns Country, Day, and Month were fixed, I could use SQL Server 2005’s PIVOT operator in a query like:

SELECT  tID
      , [Country]
      , [Day]
      , [Month]
FROM    ( SELECT    t2.tID
                  , t1.ColName
                  , t2.Txt
          FROM      Table1 AS t1
                    JOIN Table2 
                       AS t2 ON t1.ColId = t2.ColID
        ) p PIVOT ( MAX([Txt])
                    FOR ColName IN ( [Country], [Day],
                                     [Month] ) ) AS pvt
ORDER BY tID ;

However I need to construct this query dynamically, because the column names Country, Day, and Month are specified in a table, and can be changed independently from my query. In our case these columns are given in Table1.

In the first step to generate the final pivot query I need to create the list of columns, in this case [Country], [Day], [Month].

Since there is no string concatenation aggregator in SQL (a concatenation aggregator would not be deterministic without some order restriction), and since the column names are stored in rows of a table, I need to flatten these columns into a single row or variable. There are various solutions to achieve this. One solution would be to use a query like:

DECLARE @cols NVARCHAR(2000)
SELECT  @cols = COALESCE(@cols + ',[' + colName + ']',
                         '[' + colName + ']')
FROM    Table1
ORDER BY colName

This query works both on SQL Server 2000 and 2005. It is efficient, but some may not like it because it uses the same variable (@cols) on both sides of an assignment. Another solution that works on SQL Server 2005 only is to use XML PATH.

DECLARE @cols NVARCHAR(2000)
SELECT  @cols = STUFF(( SELECT DISTINCT TOP 100 PERCENT
                                '],[' + t2.ColName
                        FROM    Table1 AS t2
                        ORDER BY '],[' + t2.ColName
                        FOR XML PATH('')
                      ), 1, 2, '') + ']'

This second query (I’ve seen this posted by Peter Larsson) has the advantage that it does not use the @cols variable on the right hand side. I like this solution more, since this can be extended as a general string concatenation aggregate in more complex queries.

Both of the above queries generate, from Table1, the string: ‘[Country],[Day], [Month]’. This column list is used twice in the pivot query that we aim to construct. Once it is use in the list of columns that we want to retrieve, and once it is used as the list of values that should become columns. Having constructed this list of columns above, we can just concatenate it with the missing parts of the pivot query like:

DECLARE @query NVARCHAR(4000)
SET @query = N'SELECT tID, '+
@cols +'
FROM
(SELECT  t2.tID
      , t1.ColName
      , t2.Txt
FROM    Table1 AS t1
        JOIN Table2 AS t2 ON t1.ColId = t2.ColID) p
PIVOT
(
MAX([Txt])
FOR ColName IN
( '+
@cols +' )
) AS pvt
ORDER BY tID;'

Executing this with

EXECUTE(@query)

will give us the expected result: a table that is pivoted and shows columns that were specified in a table:

tID         Country    Day        Month
----------- ---------- ---------- ----------
1           US         4          July
2           US         11         Sep
3           US         25         Dec

 

My Pivot 

Friday, July 1, 2011 10:48:00 AM

DECLARE @cols NVARCHAR(2000)

SELECT @cols = COALESCE(@cols + ',[' + period + ']',

'[' + period + ']')

FROM vw_12wk

 

DECLARE @query NVARCHAR(4000)

SET @query = N'SELECT office,[L C],description, '+

@cols +'

FROM

(SELECT office, [L C],description,period, actual

FROM vw_12wk ) p

PIVOT

(

sum(actual)

FOR period IN

( '+

@cols +' )

) AS pvt

'

EXECUTE(@query)

 

---------------------

office L C description 14B 13B 12B 11B 10B 09B 08B 07B 06B 05B 04B 03

0 FN4 TOTAL C/S HRS 14630 23164 23692 23257 11527 17333 21899 18658 19449 21763 21609 17653

(1 row(s) affected)

 -------------------

DECLARE @cols NVARCHAR(2000)

SELECT @cols = COALESCE(@cols + ',[' + period + ']',

'[' + period + ']')

FROM vw_12wk

 

DECLARE @query NVARCHAR(4000)

SET @query = N'SELECT office,[L C],description, '+

@cols +'

FROM

(SELECT office, [L C],description,period, actual

FROM tblTrend ) p

PIVOT

(

sum(actual)

FOR period IN

( '+

@cols +' )

) AS pvt

'

EXECUTE(@query)

---------

DECLARE @cols NVARCHAR(2000)

SELECT @cols = COALESCE(@cols + ',[' + period + ']',

'[' + period + ']')

FROM vw_12wk

 

DECLARE @query NVARCHAR(4000)

SET @query = N'SELECT office,[L C],description, '+

@cols +'

FROM

(SELECT office, [L C],description,period, actual

FROM tblTrend ) p

PIVOT

(

sum(actual)

FOR period IN

( '+

@cols +' )

) AS pvt

'

EXECUTE(@query)

 

Bulk insert SQL Batchfile 

Tuesday, June 28, 2011 1:01:00 PM

CODE

sqlcmd -U [user id] -P [password] -S [server] -d [database] -i [full path to script] -o [full path to log file]

example
 

CODE

sqlcmd -U foo -P bar -S .\SqlExpress -d MyDB -i "c:\scripts\restore.sql" -o "c:\db logs\restore.log"

-o is good for logging errors and print statments. without it errors are not logged (i think.)