script all objects of a database in sql server

Scripting all objects of a database in SQL Server can be done easily by right clicking on the database and to go Tasks -> Generate Scripts.

This will show up a dialog box which will allow you to script all or selected objects (schema, stored procedures, tables, users and views) with specific options.

Advertisements

Difference between Truncate, Delete and Drop in SQL

TRUNCATE

This operation doesn’t affect the table schema.

Advantages when compared to Delete operation:

  • Faster and uses less resources.
  • Doesn’t log each row that’s deleted.
  • Requires fewer locks – locks are obtained for the whole table and the page but not on each row.
  • If table contains an identity column, the corresponding counter is reset to the seed value for the column.
  • After truncate operation is completed, zero pages are left in the table without any exception.
  • After truncate, the pages can be immediately reused in other areas.

Disadvantages:

  • Doesn’t allow WHERE condition.
  • Cannot be used if table has foreign key constraints.
  • Cannot be used if the table participates in an indexed view.
  • If the table is published using transactional replication or merge replication.
  • Cannot activate triggers because row level locks are not used. On Update,Delete,Insert triggers won’t fire
  • Rows deleted cannot be restored.

DELETE

This operation doesn’t affect the schema.

DELETE FROM TABLE achieves the same result as Truncate but in a less efficient way.

Advantages

  • Locks each individual row for deletion and so rows deleted can be restored if needed.
  • Logs each row that has been deleted.
  • Retains the counter value for Identity column.
  • Allows WHERE condition.

Disadvantages

  • Slow and uses more resources
  • Empty pages cannot be reused until a background cleanup process de-allocates the pages.

DROP

Deletes the data as well as the schema.

  • Cannot be used on a table with foreign key constraint