Monday, December 19, 2011

TOAD Vs. SSMS + Tools Pack

PROBLEM:
You are using TOAD for Oracle / SQL Server and searching for SSMS (SQL Server Management Studio) extensions to format SQL, generate insert statements, etc. Like in Toad

SOLUTION:
SSMS Tools Pack is the ultimate add-in for SSMS with the following great functionality:

CRUD (Create, Read, Update, Delete) stored procedure generation

Format SQL

Generate Insert statements for a database

Generate Insert statements for all grid results

Run one script on multiple databases

Search Database Data

Search text in grid results

SQL History Search - Query Execution History

Windows Connection Coloring

See more here: http://www.ssmstoolspack.com


If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!

Sunday, December 18, 2011

Get list of referring table and column names

PROBLEM:
You want to get list of referring table and column names

SOLUTION:
To get list of referring table and column names

SELECT
       T.NAME AS TableWithForeignKey,
       FK.CONSTRAINT_COLUMN_ID AS FK_PartNo,
       C.NAME AS ForeignKeyColumn
FROM
       SYS.FOREIGN_KEY_COLUMNS AS FK
       INNER JOIN SYS.TABLES AS T ON FK.PARENT_OBJECT_ID = T.OBJECT_ID
       INNER JOIN SYS.COLUMNS AS C ON FK.PARENT_OBJECT_ID = C.OBJECT_ID
       AND FK.PARENT_COLUMN_ID = C.COLUMN_ID
WHERE
       FK.REFERENCED_OBJECT_ID = (
SELECT OBJECT_ID
FROM SYS.TABLES
WHERE NAME = 'MY_TABLE_NAME')
ORDER BY
       TableWithForeignKey,
       FK_PartNo

To get names of foreign key constraints

SELECT DISTINCT NAME
FROM SYS.OBJECTS
WHERE OBJECT_ID IN
(
SELECT FK.CONSTRAINT_OBJECT_ID FROM SYS.FOREIGN_KEY_COLUMNS AS FK
WHERE FK.REFERENCED_OBJECT_ID =
(SELECT OBJECT_ID FROM SYS.TABLES WHERE NAME = 'MY_TABLE_NAME')
)

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!


What is ROW_NUMBER function and how to use it for paging?

PROBLEM:
What is ROW_NUMBER function and how to use it for paging?

SOLUTION:
The ROW_NUMBER function needs an OVER clause. In this example the OVER clause contains an ORDER BY clause. This just tells the ROW_NUMBER function in what order to generate the row numbers. It's possible to have multiple ROW_NUMBER functions in a single SELECT statement each with their own ordering

SELECT
ROW_NUMBER() OVER (ORDER BY LastName, FirstName) AS RowNumber,
employeeID, LastName, FirstName, City
FROM Employees;


The following example returns a subset of rows with numbers 50 to 60 inclusive in the order of the OrderDate. This is usefull for PAGING

WITH OrderedOrders AS
(
    SELECT SalesOrderID, OrderDate,
    ROW_NUMBER() OVER (ORDER BY OrderDate) AS 'RowNumber'
    FROM SalesOrderHeader
)
SELECT *
FROM OrderedOrders
WHERE RowNumber BETWEEN 50 AND 60;


The following using the ROW_NUMBER function with the PARTITION BY argument. The PARTITION BY argument is grouping the ORDER BY clause

SELECT FirstName, LastName, EmailPromotion,
ROW_NUMBER() OVER(PARTITION BY EmailPromotion ORDER BY SalesYTD) AS 'Row Number', SalesYTD
FROM vSalesPerson
WHERE TerritoryName IS NOT NULL AND SalesYTD <> 0;




If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!

How to query by digits in a char column

PROBLEM:
You have a char column that can has (logically) only two types of data for each record: digits or text. You want to make a query using comparison operators (Greater than, Less than..) to fetch only records contains digits.
You can use the isnumeric() function, but the following problems occur:


SELECT ISNUMERIC('1212') --> 1: exactly
SELECT ISNUMERIC('12C2') --> 0: exactly
SELECT ISNUMERIC('10E0') --> 1: wrong
SELECT ISNUMERIC('1,100') --> 1: wrong
SELECT ISNUMERIC('+') --> 1: wrong
SELECT ISNUMERIC('-') --> 1: wrong


SOLUTION:
As mentioned above, values like minus sign '-' and the plus sign '+' by themselves will satisfy isnumeric() but cannot be used for the "is digit" query. For this, it is better to choose the wildcard approach


SELECT
    digit_idnum
FROM
(
    select case
            IsNull((select 1
                    where idnum not like '%[^0-9]%'),0)
            when 1 then CAST(idnum as int)
            end as digit_idnum
    from my_table
) AS t
WHERE
digit_idnum >=  200 and
digit_idnum <=  700



If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!





Wednesday, December 14, 2011

Clean / Delete all data in Azure database

PROBLEM:
You want to clean / delete all data form all tables in Azure database. You cannot use sp_MSForEachTable to generate delete script

SOLUTION:
Run the following code and save the result as a script, then execute it. You can use this script also in a regular SQL Server
SELECT 'print ''NOCHECK CONSTRAINT ' + f.name + '''; ALTER TABLE ' + '[' + OBJECT_NAME(f.parent_object_id)+ ']'+' NOCHECK CONSTRAINT ' + '[' + f.name + '];'
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
UNION ALL
SELECT 'print ''delete ' + name + '''; DELETE [' + name + '];'
FROM sys.tables
UNION ALL
SELECT 'print ''CHECK CONSTRAINT ' + f.name + '''; ALTER TABLE ' + '[' + OBJECT_NAME(f.parent_object_id)+ ']'+' CHECK CONSTRAINT ' + '[' + f.name + '];'
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!


Thursday, December 8, 2011

Main differences between SQL Server 2000 and 2008

PROBLEM:
You are a developer, you want more database programming capabilities, why to upgrade from SQL Server 2000 to 2008?

SOLUTION:
The following is a list of the main differences

Sql Server 2000 Sql Server 2005 Sql Server 2008
Query Analyser and Enterprise manager are separate Both are combined as SSMS(Sql Server management Studio) Both are combined as SSMS(Sql Server management Studio)
No XML datatype is used XML datatype is introduced XML datatype is used
N/A Exception Handling Exception Handling
N/A Varchar(Max) data type Varchar(Max) data type
N/A DDL Triggers DDL Triggers
N/A DataBase Mirroring DataBase Mirroring
N/A RowNumber function for paging RowNumber function for paging
N/A Table fragmentation Table fragmentation
N/A Full Text Search Full Text Search
N/A Bulk Copy Update Bulk Copy Update
N/A Cant encrypt Can encrypt the entire database introduced in 2008
Cant compress the tables and indexes Can Compress tables and indexes (Introduced in 2005 SP2) Can compress tables and indexes
Datetime datatype is used for both date and time Datetime is used for both date and time Date and time are seperately used for date and time datatype, geospatial, timestamp, timezone  is used
No varchar(max) or varbinary(max) is available Varchar(max) and varbinary(max) is used Varchar(max) and varbinary(max) is used
No table datatype is included No table datatype is included Table datatype introduced
No SSIS is included SSIS is started using SSIS avails in this version
CMS is not available CMS is not available Central Management Server(CMS) is Introduced
PBM is not available PBM is not available Policy based management(PBM) server is Introduced
PIVOT and UNPIVOT functions are not used PIVOT and UNPIVOT functions are used PIVOT and UNPIVOT functions are used

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!


Sunday, December 4, 2011

Draw Christmas tree

PROBLEM:
You want to draw a Christmas tree using SQL query

SOLUTION:
Run the following query in Oracle database


SET heading off
SET feedback off
SET pagesize 2000

SELECT DECODE(SIGN(FLOOR(maxwidth/2)-ROWNUM)
,1,LPAD( ' ', FLOOR(maxwidth/2)-(ROWNUM-1))
|| RPAD( '*', 2*(ROWNUM-1)+1, ' *')
,LPAD( '* * *', FLOOR(maxwidth/2)+3))
FROM all_objects
,(SELECT 40 AS maxwidth FROM dual)
WHERE ROWNUM < FLOOR(maxwidth/2) +5;


If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!