SQL Server Error Messages

Get all the error message available in our server

SELECT        
    *
FROM        
    sys.messages
WHERE        
    message_id >= 0 AND
    language_id = 1033

ErrorLogging in EnterpriseLibrary Step by Step

[step 1]

[step 2]

[step 3]

[step 4]

[step 5]

[step 6]

[step 7]

[step 8]

[step 9]

Finding size of Table/Indexes in SQL Server 2005

Original file

http://www.extremeexperts.com/sql/Scripts/FindSizeOfTable.aspx

Updated query

WITH table_space_usage
( schema_name, table_name, index_name, used, reserved, ind_rows, tbl_rows )
AS (
SELECT s.Name
     , o.Name
     , COALESCE(i.Name, 'HEAP')
     ,p.used_page_count -- total_used_pages
     ,p.reserved_page_count * 8 --total_reserved_pages
     ,p.row_count --rows
     , case when i.index_id in ( 0, 1 ) then p.row_count else 0 end
  FROM sys.dm_db_partition_stats p
  INNER JOIN sys.objects as o
    ON o.object_id = p.object_id
  INNER JOIN sys.schemas as s
    ON s.schema_id = o.schema_id
  LEFT OUTER JOIN sys.indexes as i
    on i.object_id = p.object_id and i.index_id = p.index_id
 WHERE o.type_desc = 'USER_TABLE'
   and o.is_ms_shipped = 0
)
 SELECT t.schema_name
     , t.table_name
     , t.index_name
     , sum(t.used) as used_in_kb
     , sum(t.reserved) as reserved_in_kb
     , case grouping(t.index_name)
    when 0 then sum(t.ind_rows)
    else sum(t.tbl_rows) end as rows
 FROM table_space_usage as t
 GROUP BY
       t.schema_name
     , t.table_name
     , t.index_name
 WITH ROLLUP
 ORDER BY
      grouping(t.schema_name)
    , t.schema_name
    , grouping(t.table_name)
    , t.table_name
    , grouping(t.index_name)
    , t.index_name

Numeric Validation Using JavaScript

<asp:textbox id="TextBox1" runat="server"
onkeypress="return detectEvent(event);"
onkeydown="return detectEvent(event);"></asp:textbox>

onkeydown
onkeypress


we are using both the event since some
browsers don't have one of the event.


To accept only numbers both from NUMPAD and
from KeyBoard we use this method.

function detectEvent(e) {
var evt = e || window.event;
// Firefox
if(evt.type == 'keydown') {
 if ( (evt.keyCode >= 48 && evt.keyCode <= 57) || (evt.keyCode >= 96
                      && evt.keyCode <= 105) ) return true;
 return false;
 }
else if(evt.type == 'keypress') {
 if (evt.charCode >= 48 && evt.charCode <= 57) return true;
 return false;
 }
}

This one for IE  since IE don't have charCode

function detectEvent(e) {
var evt = e || window.event;
// IE
if(evt.type == 'keydown') {
 if ( (evt.keyCode >= 48 && evt.keyCode <= 57) || (evt.keyCode >= 96
                      && evt.keyCode <= 105) ) return true;
 return false;
 }
else if(evt.type == 'keypress') {
 if (evt.keyCode >= 48 && evt.keyCode <= 57) return true;
 return false;
 }
}


Now a common method for both the things(it works both in IE and FireFox
for both NUMPAD and for KeyBoard)

function validKey(e) {
var KeyID = window.event ? event.keyCode : e.which;
var evt = e || window.event;
if(evt.type == 'keydown') {
 if ( (KeyID >= 48 && KeyID <= 57) || (KeyID >= 96 && KeyID <= 105) ) return true;
 return false;
 }
else if(evt.type == 'keypress') {
 if(KeyID >= 48 && KeyID <= 57) return true;
 return false;
 }
}

Table Reference Levels

CREATE FUNCTION RISequence
(
)
RETURNS @RISEQUENCE TABLE
(
        TABLE_NAME SYSNAME,
        PRIORITY SMALLINT NOT NULL,
        PRIMARY KEY
        (
                TABLE_NAME))
AS
        --Returns The user tables and the "depth" of the table in the RI chain.
        --Tables with no FK's are given a PRIORITY of 0
        --Circular reference tables have a minimum PRIORITY of 1
BEGIN
        --COUNTER
        DECLARE @COUNTER  INT,
                @PRIORITY INT
                --RI Table Matching
        DECLARE @RIMATCH TABLE (PK_TABLE SYSNAME NOT NULL, FK_TABLE SYSNAME NOT NULL)
                --Populate @RIMATCH -- Remove Self referenced entities (WHERE expression)
                INSERT @RIMATCH
                        (
                                PK_TABLE,
                                FK_TABLE
                        )
                SELECT  PK.TABLE_SCHEMA + '.' + PK.TABLE_NAME          AS PK_TABLE ,
                        FK.TABLE_SCHEMA + '.' + FK.TABLE_NAME          AS FK_TABLE
                FROM    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS     AS RC
                        INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS FK
                        ON      FK.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
                        INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS PK
                        ON      PK.CONSTRAINT_NAME       = RC.UNIQUE_CONSTRAINT_NAME
                WHERE   FK.TABLE_SCHEMA + FK.TABLE_NAME != PK.TABLE_SCHEMA + PK.TABLE_NAME
                        --No FK's (PRIORITY=0)
                        SET @PRIORITY = 0
                INSERT @RISequence
                        (
                                TABLE_NAME,
                                PRIORITY
                        )
                SELECT  TABLE_SCHEMA + '.' + TABLE_NAME,
                        @PRIORITY
                FROM    INFORMATION_SCHEMA.TABLES AS T
                WHERE   TABLE_TYPE  = 'BASE TABLE'
                    AND TABLE_NAME != 'dtproperties'
                    AND NOT EXISTS
                        (SELECT 1
                        FROM    @RIMATCH C
                        WHERE   C.FK_TABLE = T.TABLE_SCHEMA + '.' + T.TABLE_NAME
                        )
                        --Circular Reference (CR) PRIORITY=1 ie The only FK is the circular FK
                        SET @PRIORITY = @PRIORITY + 1
                INSERT @RISequence
                        (
                                TABLE_NAME,
                                PRIORITY
                        )
                SELECT  S.FK_TABLE,
                        @PRIORITY
                FROM    @RIMATCH S
                WHERE   EXISTS
                        (SELECT 1
                        FROM    @RIMATCH X
                        WHERE   X.PK_TABLE = S.FK_TABLE
                            AND X.FK_TABLE = S.PK_TABLE
                        )
                    AND NOT EXISTS
                        (SELECT 1
                        FROM    @RISequence C
                        WHERE   C.TABLE_NAME = S.FK_TABLE
                        )
                GROUP BY S.FK_TABLE
                HAVING COUNT(         *) =
                        (SELECT COUNT(*)
                        FROM    @RIMATCH K
                        WHERE   S.FK_TABLE = K.FK_TABLE
                        )
                        --Fudge a DO operation
                        SET @COUNTER = 1 WHILE @Counter > 0 BEGIN
                        SET @COUNTER = 0
                --Standard RI
                INSERT @RISequence
                        (
                                TABLE_NAME,
                                PRIORITY
                        )
                SELECT  K.FK_TABLE ,
                        @PRIORITY
                FROM    @RIMATCH AS K
                        INNER JOIN @RISequence O
                        ON      O.TABLE_NAME = K.PK_TABLE
                WHERE   NOT EXISTS
                        (SELECT 1
                        FROM    @RISequence F
                        WHERE   K.FK_TABLE = F.TABLE_NAME
                        )
                GROUP BY K.FK_TABLE
                HAVING COUNT(         *) =
                        (SELECT COUNT(*)
                        FROM    @RIMATCH S
                        WHERE   S.FK_TABLE = K.FK_TABLE
                        )
                        SET @COUNTER =
                        CASE @@ROWCOUNT
                                WHEN 0
                                THEN 0
                                ELSE 1
                        END
                        --Because of the forced discovery of PRIORITY 1 CR, increment takes place here
                        SET @PRIORITY = @PRIORITY + 1
                --Circular References with related RI at the current PRIORITY Level
                INSERT @RISequence
                        (
                                TABLE_NAME,
                                PRIORITY
                        )
                SELECT  S.FK_TABLE,
                        @PRIORITY
                FROM    @RIMATCH S
                        INNER JOIN @RIMATCH X
                        ON      X.PK_TABLE = S.FK_TABLE
                            AND X.FK_TABLE = S.PK_TABLE
                WHERE   NOT EXISTS
                        (SELECT 1
                        FROM    @RISequence W
                        WHERE   S.FK_TABLE = W.TABLE_NAME
                        )
                    AND EXISTS
                        (SELECT 1
                        FROM    @RIMATCH Z
                        WHERE   S.PK_TABLE  = Z.FK_TABLE
                            AND Z.PK_TABLE != S.FK_TABLE
                            AND EXISTS
                                (SELECT 1
                                FROM    @RISequence A
                                WHERE   Z.PK_TABLE = A.TABLE_NAME
                                )
                        )
                        SET @COUNTER = @COUNTER +
                        CASE @@ROWCOUNT
                                WHEN 0
                                THEN 0
                                ELSE 1
                        END
        END RETURN
END GO




SELECT  *
FROM    dbo.RISequence()
ORDER BY PRIORITY,
        TABLE_NAME
        --DROP FUNCTION RISequence