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

 

Normal Order By Output

 

Order By using Case

 

Order By Output using Case

 

 

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