/* ------------------------ My Meta Content Here SEO ------------------------ */

Pages

Main Menu

Thursday, May 19, 2016

Anup Shah on WPF and Silverlight (Programming Garden): Create ASPNETDB database using aspnet_regsql tool

Anup Shah on WPF and Silverlight (Programming Garden): Create ASPNETDB database using aspnet_regsql tool: What is ASPNETDB? Microsoft has introduced a powerful default database schema in ASP.Net 2.0 as ASPNETDB.mdf database. This database fil...
Read More »

Tuesday, May 3, 2016

SQL SERVER QUERY Part-1 Script to find database role given to logins in all the databases.


/*
Description     : Script to find database role given to logins in all the databases.
Parameters    : NONE.
Returns    : NA
Create date : 4/May/2016
*/

     create table ##t2(
     DBname varchar(300),
     DBrole_name varchar(300),
     Type_dbrole varchar(300),
     Grantee varchar(300))

            insert into ##t2 exec
                        sp_msforeachdb 'use [?];
                        select  db_name() [DB name],prin.name,prin.type_desc,un.name from sys.database_role_members rl left join
                        sys .database_principals prin on rl.role_principal_id =prin.principal_id
                        join sys.sysusers un on rl.member_principal_id=un.uid'

      select DBname,Grantee,DBrole_name,Type_dbrole from  ##t2
 /* where Grantee like '%provide_the_login_name%' */
 order by DBname,Grantee

      drop table ##t2

_________________________________________________________________________________

  1. All Database User accounts and all permission granted on a specific DB.
  2. Permissions of a specific DB User on a specific DB.
  3. All permissions that a certain login account possesses on all DBs in the instances.


CREATE proc [dbo].[DBASP_ALL_UserPermission]
AS
DECLARE @strSQL nvarchar(2000),
@dbname nvarchar(256)
IF OBJECT_ID('tempdb..#DBUsers') IS NOT NULL DROP TABLE #DBUsers
CREATE table #DBUsers 
(
DBname varchar (256),  
LoginName varchar(100),  
DBUserName varchar(100),           
[DBRole] varchar (100),     
PrincipalType  varchar(100), 
PermissionName  varchar(100) ,
ObjectType varchar(50),  
Objectname varchar(100), 
Columnname varchar(100)
)  
DECLARE listdbs Cursor
FOR
SELECT name from master.dbo.sysdatabases
WHERE  name not in ('master', 'model', 'msdb', 'tempdb')
OPEN listdbs
FETCH next
     FROM  listdbs into @dbname    
     WHILE @@fetch_status = 0
     BEGIN   
     SELECT @strSQL =                      
    '
     Use ['+ @dbname+'] ;
     SELECT 
      DB_name()
     ,sp.name 
     ,dp.name    
     ,dp2.name
     ,dp.type_desc
     ,perm.permission_name
     , objectType = case perm.class
             WHEN 1 THEN obj.type_desc
                      ELSE perm.class_desc
     END
     ,objectName = case perm.class
              when 1 then Object_name(perm.major_id)
                    when 3 then schem.name 
                             when 4 then imp.name
     END
                     , col.name
     FROM
     sys.database_role_members drm
     RIGHT JOIN  sys.database_principals dp
     on dp.principal_id = drm.member_principal_id
     LEFT JOIN sys.database_principals dp2
     on dp2.principal_id = drm.role_principal_id
     FULL JOIN sys.server_principals sp 
     ON dp.[sid] = sp.[sid] 
     LEFT JOIN sys.database_permissions perm 
     ON perm.[grantee_principal_id] = dp.[principal_id]
     LEFT JOIN sys.columns col 
     ON col.[object_id] = perm.major_id 
     AND col.[column_id] = perm.[minor_id] 
     LEFT JOIN sys.objects obj 
     ON perm.[major_id] = obj.[object_id] 
     LEFT JOIN sys.schemas schem 
     ON schem.[schema_id] = perm.[major_id] 
     LEFT JOIN sys.database_principals imp 
     ON imp.[principal_id] = perm.[major_id] 
     WHERE dp.name not in (''sys'' , ''information_schema'' , ''guest'', ''public'')
     ORDER by sp.name
    '
    INSERT into #DBUsers
    EXEC (@strSQL)
    FETCH NEXT
    FROM listdbs into @dbname
    END
    CLOSE listdbs
    DEALLOCATE listdbs
    SELECT * from #DBUsers 

    GO
    
EXEC DBASP_ALL_UserPermission










Read More »

Friday, March 18, 2016

C# Collection & Generics Tutorials Array, ArrayList, Deep Copy vs Shallow Copy etc.,

C# Collection & Generics Tutorials

Collections are data structures that holds data in different ways for flexible operations . C# Collection classes are defined as part of the System.Collections or System.Collections.Generic namespace.
How to use C# ArrayList Class

ArrayList is one of the most flexible data structure from CSharp Collections. ArrayList contains a simple list of values. ArrayList implements the IList interface using an array and very easily we can add , insert , delete , view etc. It is very flexible because we can add without any size information , that is it will grow dynamically and also shrink .

c-sharp-arraylist

  Add : Add an Item in an ArrayList
  Insert : Insert an Item in a specified position in an ArrayList
  Remove : Remove an Item from ArrayList
  RemoveAt: remove an item from a specified position
  Sort : Sort Items in an ArrayList

How to add an Item in an ArrayList ?

  Syntax : ArrayList.add(object)
  object : The Item to be add the ArrayList
  ArrayList arr;
  arr.Add("Item1");

How to Insert an Item in an ArrayList ?
  Syntax : ArrayList.insert(index,object)
  index : The position of the item in an ArrayList
  object : The Item to be add the ArrayList
  ArrayList arr;
  arr.Insert(3, "Item3");

How to remove an item from arrayList ?
  Syntax : ArrayList.Remove(object)
  object : The Item to be add the ArrayList
  arr.Remove("item2")

How to remove an item in a specified position from an ArrayList ?
  Syntax : ArrayList.RemoveAt(index)
  index : the position of an item to remove from an ArrayList
  ItemList.RemoveAt(2)

How to sort ArrayList ?
  Syntax : ArrayList.Sort()

The following CSharp source code shows some function in ArrayList

using System;
using System.Collections;
using System.Windows.Forms;

namespace WindowsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            int i = 0;
            ArrayList ItemList = new ArrayList();
            ItemList.Add("Item4");
            ItemList.Add("Item5");
            ItemList.Add("Item2");
            ItemList.Add("Item1");
            ItemList.Add("Item3");
            MessageBox.Show ("Shows Added Items");
            for (i = 0; i < = ItemList.Count - 1; i++)
            {
                MessageBox.Show(ItemList[i].ToString());
            }
            //insert an item
            ItemList.Insert(3, "Item6");
            //sort itemms in an arraylist
            ItemList.Sort();
            //remove an item
            ItemList.Remove("Item1");
            //remove item from a specified index
            ItemList.RemoveAt(3);
            MessageBox.Show("Shows final Items the ArrayList");
            for (i = 0; i < = ItemList.Count - 1; i++)
            {
                MessageBox.Show(ItemList[i].ToString());
            }
        }
    }
}

When you execute this C# program , at first add five items in the arraylist and displays. Then again one more item inserted in the third position , and then sort all items. Next it remove the item1 and also remove the item in the third position . Finally it shows the existing items.

Qns.  What the difference is between add and insert method in arraylist   ?

Ans. Add will always add the element at the end of the ArrayList while Insert allows you to choose what index you want your element to be. If you always want the element placed at the end of the list then Add is what you. 



How to use C# HashTable Class

Hashtable in C# represents a collection of key/value pairs which maps keys to value. Any non-null object can be used as a key but a value can. We can retrieve items from hashTable to provide the key . 
Both keys and values are Objects.

The commonly used functions in Hashtable are :
  Add                      : To add a pair of value in HashTable
  ContainsKey     : Check if a specified key exist or not
  ContainsValue : Check the specified Value exist in HashTable
  Remove             : Remove the specified Key and corresponding Value

Add : To add a pair of value in HashTable

  Syntax : HashTable.Add(Key,Value)
  Key : The Key value
  Value : The value of corresponding key
  Hashtable ht;
  ht.Add("1", "Sunday");

ContainsKey : Check if a specified key exist or not

  Synatx : bool HashTable.ContainsKey(key)
  Key                      : The Key value for search in HahTable
  Returns : return true if item exist else false
  ht.Contains("1");

ContainsValue : Check the specified Value exist in HashTable

  Synatx : bool HashTable.ContainsValue(Value)
  Value : Search the specified Value in HashTable
  Returns : return true if item exist else false
  ht.ContainsValue("Sunday")

Remove : Remove the specified Key and corresponding Value

  Syntax : HashTable.Remove(Key)
  Key : The key of the element to remove
  ht.Remove("1");


The following source code shows some important operations in a HashTable

using System;
using System.Collections;
using System.Windows.Forms;
 
namespace WindowsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
 
        private void button1_Click(object sender, EventArgs e)
        {
            Hashtable weeks = new Hashtable();
            weeks.Add("1", "SunDay");
            weeks.Add("2", "MonDay");
            weeks.Add("3", "TueDay");
            weeks.Add("4", "WedDay");
            weeks.Add("5", "ThuDay");
            weeks.Add("6", "FriDay");
            weeks.Add("7", "SatDay");
            //Display a single Item 
 
            MessageBox.Show(weeks["5"].ToString ());
            //Search an Item 
            if (weeks.ContainsValue("TueDay"))
            {
                MessageBox.Show("Find");
            }
            else
            {
                MessageBox.Show("Not find");
            }
            //remove an Item 
            weeks.Remove("3");
            //Display all key value pairs
            foreach (DictionaryEntry day in weeks )
            {
                MessageBox.Show (day.Key + "   -   " + day.Value );
            }
        }
    }
}
 
When you execute this C# program it will add seven entries in the hashtable. The first message it will display the item 5. Then it check the value "TueDay" is existing or not . Next it remove the third item from HashTable. Finaly it displays all items exist in the HashTable.



How to use C# Stack Class

The Stack class represents a last-in-first-out (LIFO) Stack of Objects. Stack follows the push-pop operations. That is we can Push (insert) Items into Stack and Pop (retrieve) it back . Stack is implemented as a circular buffer. It follows the Last In First Out (LIFO) system. That is we can push the items into a stack and get it in reverse order. Stack returns the last item first. As elements are added to a Stack, the capacity is automatically increased as required through reallocation.

Commonly used methods :

  Push    : Add (Push) an item in the Stack data structure
  Pop      : Pop return the last Item from the Stack
  Contains: Check the object contains in the Stack

Push : Add (Push) an item in the Stack data structure

  Syntax : Stack.Push(Object)
  Object : The item to be inserted.
  Stack days = new Stack();
  days.Push("Sunday");

Pop        : Pop return the item last Item from the Stack

  Syntax : Object Stack.Pop()
  Object : Return the last object in the Stack
  days.Pop();

Contains : Check the object contains in the Stack

  Syntax : Stack.Contains(Object)
  Object : The specified Object to be search
  days.Contains("Tuesday");



The following CSharp Source code shows some of important functions in Stack Class:

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
 
namespace WindowsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
 
        private void button1_Click(object sender, EventArgs e)
        {
            Stack days = new Stack();
            days.Push("SunDay");
            days.Push("MonDay");
            days.Push("TueDay");
            days.Push("WedDay");
            days.Push("ThuDay");
            days.Push("FriDay");
            days.Push("SaturDay");
            if (days.Count ==7)
            {
                MessageBox.Show(days.Pop().ToString ());
            }
            else
            {
                MessageBox.Show("SaturDay does not exist");
            }
        }
    }
}
 
When you execute this C# program add seven items in the stack . Then it check the count is equal to 7 , if it is seven then pop() the item. The message box will display SaturDay.



How to use C# Queue Class

The Queue works like FIFO system , a first-in, first-out collection of Objects. Objects stored in a Queue are inserted at one end and removed from the other. The Queue provide additional insertion, extraction, and inspection operations. We can Enqueue (add) items in Queue and we can Dequeue (remove from Queue ) or we can Peek (that is we will get the reference of first item ) item from Queue. Queue accepts null reference as a valid value and allows duplicate elements.

Some important functions in the Queue Class are follows :

  Enqueue : Add an Item in Queue
  Dequeue : Remove the oldest item from Queue
  Peek      : Get the reference of the oldest item

Enqueue : Add an Item in Queue
  Syntax : Queue.Enqueue(Object)
  Object : The item to add in Queue
  days.Enqueue("Sunday");

Dequeue : Remove the oldest item from Queue (we don't get the item later)

  Syntax : Object Queue.Dequeue()
  Returns : Remove the oldest item and return.
  days.Dequeue();

Peek : Get the reference of the oldest item (it is not removed permanently)

  Syntax : Object Queue.Peek()
  returns : Get the reference of the oldest item in the Queue
  days.peek();



The following CSharp Source code shows some of commonly used functions :

using System;
using System.Collections;
using System.Windows.Forms;
 
namespace WindowsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
 
        private void button1_Click(object sender, EventArgs e)
        {
            Queue days = new Queue();
            days.Enqueue("Sunday");
            days.Enqueue("Monday");
            days.Enqueue("Tuesday");
            days.Enqueue("Wednsday");
            days.Enqueue("Thursday");
            days.Enqueue("Friday");
            days.Enqueue("Saturday");
 
            MessageBox.Show (days.Dequeue().ToString ());
            
            if (days.Contains("Monday"))
            {
                MessageBox.Show("The queue contains Monday");
            }
            else
            {
                MessageBox.Show("Does not match any entries");
            }
        }
    }
}
When you execute the above C# source code , you will get Sunday in the message box and then it check the Monday is exist in the queue or not.
How to use C# NameValueCollection Class
NameValueCollection is used to store a collection of associated String keys and String values that can be accessed either with the key or with the index. It is very similar to C# HashTable, HashTable also stores data in Key , value format .
NameValueCollection can hold multiple string values under a single key. As elements are added to a NameValueCollection, the capacity is automatically increased as required through reallocation. The one important thing is that you have to import System.Collections.Specialized Class in your program for using NameValueCollection.
Adding new pairs
  NameValueCollection.Add(name,value)
  NameValueCollection pair = new NameValueCollection();
  pair.Add("High", "80");
Get the value of corresponding Key
  string[] NameValueCollection.GetValues(index);
  NameValueCollection pair = new NameValueCollection();
  pair.Add("High", "80");
  string[] vals = pair.GetValues(1);
using System;
using System.Collections;
using System.Windows.Forms;
using System.Collections.Specialized;
 
namespace WindowsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
 
        private void button1_Click(object sender, EventArgs e)
        {
            NameValueCollection markStatus = new NameValueCollection();
            string[] values = null;
 
            markStatus.Add("Very High", "80");
            markStatus.Add("High", "60");
            markStatus.Add("medium", "50");
            markStatus.Add("Pass", "40");
 
            foreach (string key in markStatus.Keys)
            {
                values = markStatus.GetValues(key);
                foreach (string value in values)
                {
                    MessageBox.Show (key + " - " + value);
                }
            } 
        }
    }
}



C# Array Examples
Arrays are using for store similar data types grouping as a single unit. We can access Array elements by its numeric index. The array indexes start at zero. The default value of numeric array elements are set to zero, and reference elements are set to null .
Integer Array
Declaring and Initializing an Integer Array
int[] array = new int[4];
array[0] = 10;
array[1] = 20;
array[2] = 30;
array[3] = 40;
In the above code we declare an Integer Array of four elements and assign the value to array index . That means we assign values to array index 0 - 4.
We can retrieve these values from array by using a for loop.
                for (int i = 0; i < array.Length; i++)
                {
                                MessageBox.Show (array[i]);
                }
Initialize Array
Also we can declare and initialize an array in one statement.
                int[] array = new int[] {10, 20, 30, 40};
Note that in the above code we did not specify the length of the array so the compiler will do it for us.
How to find the length of an Array ?
Array.Length
We can use array.Length to find the length of an Array.
String Array
Declaring and Initializing a String Array
string[] week = new string[7];
week[0] = "Sunday";
week[1] = "Monday";
The above C# code declare a string array of 7 strings and assign some values to it.
string[] week = new string[] {"Sunday","Monday","Tuesday"};
The above code declare and initialize a string array with values.
string str = week[1];
We can access the Arrays elements by providing its numerical index, the above statement we access the second value from the week Array.
In the following program , we declare an Array "week" capable of seven String values and assigns the seven values as days in a week . Next step is to retrieve the elements of the Array using a for loop . For finding the end of an Array we used the Length function of Array Object.
    string[] week = new string[7];
    week[0] = "Sunday";
    week[1] = "Monday";
    week[2] = "Tuesday";
    week[3] = "Wednsday";
    week[4] = "Thursday";
    week[5] = "friday";
    week[6] = "Saturday";
    for (int i = 0; i < = week.Length-1; i++)
    {
        MessageBox.Show(week[i]);
    }
How to resize an Array
An array can be resized with Array.Resize < T > Method , that means We make an array bigger or smaller. Array.Resize < T > Method Changes the number of elements of a one-dimensional array to the specified new size.
Array.Resize < T > - T is the type of the elements of the array.
This method should be used with only one dimensional Array. This method allocates a new array with the specified size, copies elements from the old array to the new one, and then replaces the old array with the new one.


Resize Array
    // Initialize array for example
    char[] array = new char[5];
    array[0] = 'A';
    array[1] = 'B';
    array[2] = 'C';
    array[3] = 'D';
    array[4] = 'E';
    for (int i = 0; i < array.Length; i++)
    {
        MessageBox.Show (array[i].ToString ());
    }
    Array.Resize(ref array, 3);
    for (int i = 0; i < array.Length; i++)
    {
        MessageBox.Show(array[i].ToString ());
    }
Array.Resize(ref array, 3);
In the above code we resize the array to 3 elements.
for..each loop and array

                int[] array = { 10, 30, 50 }; //array declaration
                foreach (int element in array)
                {
                    Console.WriteLine(element);
                }



Array Sort
sort array in c# ascending descending Reverse array
You can sort the arrays in ascending order as well as descending . We can use Array.Sort method for sorts the elements in a one-dimensional array. Also we can use Array.Reverse method for reverses the sequence of the elements in the entire one-dimensional Array. Click the following link to see .... How to sort C# Arrays
Sort an Integer array in Ascending order
The following source code shows how to sort an integer Array in ascending order.
C#
int[] array = new int[] { 3, 1, 4, 5, 2 };
Array.Sort(array);
foreach (var str in array)
{
    MessageBox.Show(str.ToString());
}
Sort an Integer array in Descending order
The following code shows how to sort an Integer array in reverse order.
C#
int[] array = new int[] { 3, 1, 4, 5, 2 };
Array.Sort(array);
Array.Reverse(array);
foreach (var str in array)
{
    MessageBox.Show(str.ToString());
}



LINQ OrderByDescending
You can use LINQ OrderByDescending method to reverse an array. OrderByDescending method sorts elements from high to low. The following C# source code shows how to sort an array in descending order by using LINQ OrderByDescending.
string[] array = new string[] { "a", "b", "c", "d" };
array = array.OrderByDescending(c => c).ToArray();
foreach (var str in array)
{
    MessageBox.Show(str.ToString());
}
How to Create an Array with different data types
You can create an array with elements of different data types when declare the array as Object. Since System.Object is the base class of all other types, an item in an array of Objects can have a reference to any other type of object. More about.... Multiple data types in an Array
Multiple data types in an Array
No, we cannot store multiple datatype in an Array, we can store similar datatype only in an Array.
How to Create an Array with different data types
How to store multiple datatypes in an array? interview questions and answers c# vb.net asp.net
You can create an array with elements of different data types when declare the array as Object. Since System.Object is the base class of all other types, an item in an array of Objects can have a reference to any other type of object.
Ex:
object[] mixedArray = new object[4];
mixedArray[0]=10;
mixedArray[1]="Jack";
mixedArray[2]=true;
mixedArray[3]=System.DateTime.Now;
In order to retrieve different data types from an Object array, you can convert an element to the appropriate data type.
int id = int.Parse(mixedArray(0));
DateTime admissionDate = DateTime.Parse(mixedArray(3));
NOTE: If you want to store different data types in an Array, better use System.Collections.ArrayList.
ArrayList Class
ArrayList Class in .Net framework
ArrayList is basically an alternative to an array. It is one of the most flexible data structure from .Net Framework. It also allow dynamic memory allocation(DML), adding, searching and sorting items in the list. More about.... ArrayList
Array Example
Arrays are using for store similar data types grouping as a single unit. We can access Array elements by its numeric index. The array indexes start at zero. More about.... Array
System.Array.CopyTo and System.Array.Clone()
The System.Array.CopyTo method copies the elements into another pre-existing array starting from a given index. The System.Array.Clone() method returns a new array object, which means that the destination array need not exist yet since a new one is created from scratch with containing all the elements in the original array. More about the difference between .... System.Array.CopyTo and System.Array.Clone()
Difference between System.Array.CopyTo and System.Array.Clone()
.Net interview questions and answers C# vb.net asp.net
The System.Array.CopyTo method copies the elements into another pre-existing array starting from a given index. In this case the destination array need to already exist. Moreover, the target Object needs to be sufficient to hold all the elements in the source array from the index you specify as the destination. When perform CopyTo() method both arrays must be single dimensional.
The System.Array.Clone() method returns a new array object, which means that the destination array need not exist yet since a new one is created from scratch with containing all the elements in the original array. The Clone() method works on both single and multi-dimensional arrays.
Performance wise System.Array.CopyTo is faster than Clone when copying to array of same type.
Shallow copy and Deep copy
.Net Frequently asked questions and answers C# vb.net asp.net
In .Net Shallow copy and deep copy are used for copying data between objects. Shallow copying is creating a new object and then copying the non-static fields of the current object to the new object. If the field is a value type, a bit by bit copy of the field is performed. While Deep copy is creating a new object and then copying the non-static fields of the current object to the new object. If a field is a value type, a bit by bit copy of the field is performed. More about... Difference between Shallow copy and Deep copy
Array Example
Arrays are using for store similar data types grouping as a single unit. We can access Array elements by its numeric index. The array indexes start at zero. More about.... Array
How to check if a value exists in an array ?
The following program shows how to find an element from an Array
Difference between Shallow copy and Deep copy
An object copy is a process where a data object has its attributes copied to another object of the same data type. In .Net Shallow copy and deep copy are used for copying data between objects.
What is Shallow copy ?

Shallow copying is creating a new object and then copying the non-static fields of the current object to the new object. If the field is a value type, a bit by bit copy of the field is performed. If the field is a reference type, the reference is copied but the referred object is not, therefore the original object and its clone refer to the same object. A shallow copy of an object is a new object whose instance variables are identical to the old object. In .Net shallow copy is done by the object method MemberwiseClone().
The situations like , if you have an object with values and you want to create a copy of that object in another variable from same type, then you can use shallow copy, all property values which are of value types will be copied, but if you have a property which is of reference type then this instance will not be copied, instead you will have a reference to that instance only.
What is Deep copy ?

Deep copy is creating a new object and then copying the non-static fields of the current object to the new object. If a field is a value type, a bit by bit copy of the field is performed. If a field is a reference type, a new copy of the referred object is performed. A deep copy of an object is a new object with entirely new instance variables, it does not share objects with the old. While performing Deep Copy the classes to be cloned must be flagged as [Serializable].
Deep copy is intended to copy all the elements of an object, which include directly referenced elements of value type and the indirectly referenced elements of a reference type that holds a reference to a memory location that contains data rather than containing the data itself.



Read More »

Friday, March 4, 2016

T-SQL: SQL SERVER CURSOR A Simple Example Using a Cursor

T-SQL: SQL SERVER CURSOR A Simple Example Using a Cursor

First Create Table

CREATE TABLE [dbo].[Tbl_Broadcast_Mktg](
       [Broadcast_Id] [int] IDENTITY(1,1) NOT NULL,
       [Broadcast_Campaign] [nvarchar](100) NULL,
       [Broadcast_Coupon] [int] NULL,
       [Broadcast_Custdb] [int] NULL,
       [Broadcast_Custdb_Flag] [nvarchar](50) NULL,
       [Broadcast_Date] [datetime] NULL,
       [SMS_count] [nvarchar](50) NULL,
       [total_sms_sent] [nvarchar](50) NULL,
       [total_broadcast_redemption] [nvarchar](50) NULL,
       [total_redemption_sale] [nvarchar](50) NULL,
       [total_redemption_percentage] [nvarchar](50) NULL,
       [broadcast_status_type] [nvarchar](50) NULL,
       [User_Id] [int] NULL,
       [Broadcast_Status] [int] NULL,
       [Db_type] [nvarchar](50) NULL,
       [tot_sms_count] [numeric](18, 0) NULL,
       [tot_targeted_sms_count] [numeric](18, 0) NULL,
       [tot_nontargeted_sms_count] [numeric](18, 0) NULL,
       [sms_api_code] [nvarchar](50) NULL,
       [sms_api_account] [nvarchar](50) NULL,
       [campaign_cost] [numeric](18, 3) NULL,
       [tot_sms_sent] [numeric](18, 0) NULL,
 CONSTRAINT [PK_Tbl_Broadcast_Mktg] PRIMARY KEY CLUSTERED(       [Broadcast_Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
 ALTER TABLE [dbo].[Tbl_Broadcast_Mktg] ADD  CONSTRAINT [DF_Tbl_Broadcast_Mktg_Broadcast_Date]  DEFAULT (getdate()) FOR [Broadcast_Date]
ALTER TABLE [dbo].[Tbl_Broadcast_Mktg] ADD  CONSTRAINT [DF_Tbl_Broadcast_Mktg_Broadcast_Status]  DEFAULT ((1)) FOR [Broadcast_Status]

 

Here We Will Alter Few Nvarchar Columns In To Numeric Data Type Using CURSOR


 DECLARE @DBName nvarchar(50), @ColumnName nvarchar(50), @DataType nvarchar(50)         
 DECLARE DataType_cursor CURSOR    FOR              SELECT   DB_Name() 'DBName',  c.name 'ColumnName',    t.Name 'DataType'  FROM sys.columns c       INNER JOIN              sys.types t ON c.user_type_id = t.user_type_id       LEFT OUTER JOIN              sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id       LEFT OUTER JOIN              sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id       WHERE
              c.object_id = OBJECT_ID('Tbl_Broadcast_MKtg')              and c.name in('tot_sms_sent','total_broadcast_redemption','total_redemption_sale','campaign_cost')
              and t.Name <> 'numeric'
             OPEN DataType_cursor
 FETCH NEXT FROM DataType_cursor INTO @DBName, @ColumnName, @DataType
 WHILE @@FETCH_STATUS = 0        
BEGIN                    PRINT 'DBName : '+ convert(varchar(20),@DBName)+', ColumnName : '+@ColumnName+ ', DataType : '+convert(varchar(20),@DataType)
              SELECT @DBName, @ColumnName, @DataType                                          DECLARE @SQLQuery AS NVARCHAR(500)
              /* Build Transact-SQL String with parameter value */
              SET @SQLQuery = 'alter table      Tbl_Broadcast_MKtg alter column ' + @ColumnName + ' numeric'                           /* Execute Transact-SQL String */
              EXECUTE(@SQLQuery)
        FETCH NEXT FROM DataType_cursor INTO @DBName, @ColumnName, @DataType                           ENDCLOSE DataType_cursor
 DEALLOCATE DataType_cursor
 

Alter columns of a table in multiple database server using sp_msforeachdb cursor dynamic query 

DECLARE @AllTables table (DBName nvarchar(4000),ColumnName nvarchar(4000),DataType nvarchar(4000))
DECLARE @SQL   nvarchar(4000)
SET @SQL=' USE [?]
IF DB_ID(''?'') > 4    BEGIN        USE [?]               IF EXISTS (select * from sys.tables where name = N''Tbl_Broadcast_MKtg'')                           BEGIN                                  DECLARE @DBName nvarchar(50), @ColumnName nvarchar(50), @DataType nvarchar(50)                                         DECLARE DataType_cursor CURSOR    FOR                           SELECT   DB_Name() ''DBName'',  c.name ''ColumnName'',    t.Name ''DataType''  FROM sys.columns c                           INNER JOIN                                  sys.types t ON c.user_type_id = t.user_type_id                           LEFT OUTER JOIN                                  sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id                           LEFT OUTER JOIN                                  sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id                           WHERE                                  c.object_id = OBJECT_ID(''Tbl_Broadcast_MKtg'')                                  and c.name in(''tot_sms_sent'',''total_broadcast_redemption'',''total_redemption_sale'',''campaign_cost'')                                  and t.Name <> ''numeric''                                                      OPEN DataType_cursor                                         FETCH NEXT FROM DataType_cursor INTO @DBName, @ColumnName, @DataType                      WHILE @@FETCH_STATUS = 0                                    BEGIN                                                                                               DECLARE @SQLQuery AS NVARCHAR(500)                                  /* Build Transact-SQL String with parameter value */                                  SET @SQLQuery = ''alter table     Tbl_Broadcast_MKtg alter column '' + @ColumnName + '' numeric''                                                                   /* Execute Transact-SQL String */                                  EXECUTE(@SQLQuery)                                                                   SELECT @DBName, @ColumnName, @DataType                            FETCH NEXT FROM DataType_cursor INTO @DBName, @ColumnName, @DataType                                                                   END                     CLOSE DataType_cursor                     DEALLOCATE DataType_cursor              END    END             'INSERT INTO @AllTables (DBName,ColumnName,DataType)
    EXEC sp_msforeachdb @SQLSET NOCOUNT OFF
SELECT * FROM @AllTables ORDER BY 1
  


Read More »

My Blog List

  • काश - काश मुझे भी पीने की आदत होती,मैं कब का मुर्दा हो गया होता। छुटकारा मिलता आज के आतंकवाद से, किसी संतान भूमि में सो गया होता। मेरा एतबार कौन करेगा, मैंने मुर...
    2 months ago
  • काश - काश मुझे भी पीने की आदत होती,मैं कब का मुर्दा हो गया होता। छुटकारा मिलता आज के आतंकवाद से, किसी शमशान भूमि में सो गया होता। मेरा एतबार कौन करेगा, मैंने मुर...
    2 months ago
  • Kumaon University Nainital B.Ed entrance exam test result 2012 - कुमाऊँ विश्वविधालय, नैनीताल (उत्तराखण्ड)
    10 years ago