Posted by: R Manimaran | June 3, 2009

SQL Order by based on the Field values

SQL Order by based on the Field values

 In the SQL query, we use Order By clause to order the data in the output. In the Order by we use the Fieldname with Ascending or descending option. But suppose we need to override the Order By to order the output based on the values a Field.

 

Normal Order By clause

 NormalOrderBy

Normal Order By Output

 Order By Output

Order By using Case

 OrderByQuery

Order By Output using Case

 OrderByOutput

 

SP_MSFOREACHTABLE stored Procedure in SQL SERVER

 

i.                    Print all table names with owner

 

EXEC SP_MSFOREACHTABLE @command1 =”PRINT ‘?'”

 

ii.                  Drop all the tables

 

EXEC SP_MSFOREACHTABLE @command1 =”DELETE FROM ‘?'”

 

iii.                Delete all the tables

 

EXEC SP_MSFOREACHTABLE @command1 =”DROP TABLE ‘?'”

 

iv.                Delete all the data’s inside all the tables in a Database

In a table, we may have some referential Constraint to tables. So when deleting values it will show the error message that it has reference to other table. In that situation it is difficult to search each and every reference.

 

Here is the list of Query which will delete all the table data’s

 

–List all the tables

EXEC SP_MSFOREACHTABLE @command1=”PRINT ‘?'”

 

–Disable all the Reference Integrity in all tables

EXEC SP_MSFOREACHTABLE ‘ALTER TABLE ? NO CHECK CONSTRAINT ALL’

GO

–Delete the data’s

EXEC SP_MSFOREACHTABLE

     IF OBJECTPROPERTY(object_id(“?”),”TABLEHASFOREIGNREF”)=1

         DELETE FROM ?

    ELSE

         TRUNCATE TABLE ?

GO

–Now enable reference integrity

EXEC SP_MSFOREACHTABLE ‘ALTER TABLE ? CHECK CONSTRAINT ALL’

GO

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: