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’
–Delete the data’s
DELETE FROM ?
TRUNCATE TABLE ?
–Now enable reference integrity
EXEC SP_MSFOREACHTABLE ‘ALTER TABLE ? CHECK CONSTRAINT ALL’