aiDeeva.com

Category: SQL Server 2008 R2

  • Download and Install AdventureWorks Sample Database

    Microsoft SQL Server Sample Database is moved to Microsoft’s Open Source site of CodePlex.

    You can download it from the below link.
    http://msftdbprodsamples.codeplex.com/

  • How to Create Alias in SQL Server?

    What is Alias?
    A SQL Server alias is the user friendly name. For example if there are many application databases hosted on same SQL Server. You can give the different alias name for each application. Simply Alias is an entry in a hosts file, a sort of hard coded DNS lookup a SQL Server instance.

    You can create Alias from Configuration Manager.

    Go to SQL Server Configuration manager – Go to SQL Native Client -> Right Click and SELECT New Alias from the popup window.

    Alias Name — Alernative name of SQL Server
    Port No — Specify the Port No
    Server – Mentioned the Server Name or IP address

    Make sure On a 64-bit system, if you have both 32-bit and 64-bit clients, you will need to create an alias for BOTH 32-bit and 64-bit clients.

  • Identify Objects Type using sys.SysObjects

    Today I received an email from my one of blog reader regarding identification of different objects using Sys.sysObjects.

    You can query the sys.SysObjects to get all the objects created with in the database.
    Example

    SELECT *
    FROM   sys.sysobjects
    WHERE  TYPE = ‘u’ 

    Below are the different kind of object you can retrieve from database.

    Object Type Abbreviation
    AF = Aggregate function (CLR)
    C = CHECK constraint
    D = Default or DEFAULT constraint
    F = FOREIGN KEY constraint
    L = Log
    FN = Scalar function
    FS = Assembly (CLR) scalar-function
    FT = Assembly (CLR) table-valued function
    IF = In-lined table-function
    IT = Internal table
    P = Stored procedure
    PC = Assembly (CLR) stored-procedure
    PK = PRIMARY KEY constraint (type is K)
    RF = Replication filter stored procedure
    S = System table
    SN = Synonym
    SQ = Service queue
    TA = Assembly (CLR) DML trigger
    TF = Table function
    TR = SQL DML Trigger
    TT = Table type
    U = User table
    UQ = UNIQUE constraint (type is K)
    V = View
    X = Extended stored procedure

  • Insert data from one table to another table

    You can insert the data from one table to another table using SELECT INTO and INSERT INTO with SELECT.. FROM clause.


    — Below statement will create the temp table to insert records
    select * INTO #tmpObjects from sys.sysobjects where type = ‘u’

    — Below statement will create the user table to insert records.
    — First will create the table and insert it details as well in new table
    select * INTO tmpObjects from sys.sysobjects where type = ‘u’

    –Below statement will insert new data into table
    insert into tmpObjects SELECT * from sys.sysobjects where type = ‘s’

  • Display the size of all tables in Sql Server 2005

    You can use sys.SP_SpaceUsed stored procedure to get the size of all the tables.

    Below query will calulate the space of all the tables.
    –create temp table to store the result
    CREATE TABLE #temptable
      (
         tablename    VARCHAR(100),
         numberofrows VARCHAR(100),
         reservedsize VARCHAR(50),
         datasize     VARCHAR(50),
         indexsize    VARCHAR(50),
         unusedsize   VARCHAR(50)
      )

    –Inserting into temp table

    INSERT INTO #temptable
                (tablename,
                 numberofrows,
                 reservedsize,
                 datasize,
                 indexsize,
                 unusedsize)

    EXEC Sp_msforeachtable @command1=“EXEC sp_spaceused ‘?’”

    SELECT *
    FROM   #temptable

    — drop the temporary table
    DROP TABLE #temptable 

     

    Size of All the tables