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!

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!

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!

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!

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!