SQL Server: Dropping objects from 2000, 2005, 2008

From 2000

--Drop All Constraints
select + 'alter table ' + table_schema + '.'+ table_name + ' drop constraint '+ constraint_name  from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where table_name in (
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
where table_name like 'tbl%'  );
 
                                --Drop All Views
Select 'drop view '+ name From sysobjects
Where type = 'V' and name like '%vw%';
 
--Drop All Tables
select + 'drop table ' + table_schema + '.'+ table_name
from INFORMATION_SCHEMA.TABLES
where table_name like 'tbl%';
 
 http://www.daniweb.com/web-development/databases/ms-sql/threads/51556/get-table-list-from-sql-2k-server
http://sujithcjose.blogspot.com.au/2009/01/list-all-stored-procedurestables-or.html

Comments

Post a Comment

Popular posts from this blog

SQL Server: Finding Query using SPID

A SPID in SQL Server is a Server Process ID. Every time an application connects to SQL Server, a new connection (or SPID) is created. This connection has a defined scope and memory space and cannot interact with other SPIDs. The term SPID is synonymous with Connection, or Session. Status of a Session: RUNNING: This status means session is running one or more batches. When Multiple Active Result Sets (MARS) is enabled, a session can run multiple batches. SUSPENDED : It means that the request currently is not active because it is waiting on a resource. The resource can be an I/O for reading a page, A WAIT can be communication on the network, or it is waiting for lock or a latch. It will become active once the task it is waiting for is completed. Once I/O is completed (Data for table tblStudents is available in the memory), query will move into RUNNABLE queue. RUNNABLE : The SPID is in the runnable queue of a scheduler and waiting for a quantum to run on the sche...
Read more

Restoring Master, Model and MSDB from Netbackup

Restoring System Databases got two parts: Taking the instance in single user mode and Restoring the Master Database If you need to rebuild the system databases on top of this you can use the below command: "C:\Program Files\Microsoft SQL Server\ 100 \Setup Bootstrap\ SQLServer2008R2 \setup.exe" /ACTION=REBUILDDATABASE /INSTANCENAME=SQL008 /SQLSYSADMINACCOUNTS=domain\ServiceUserName /SAPWD="yourpasswordOfSAuser" /SQLCOLLATION="Latin1_General_CI_AS" here 100 is for SQL Server 2008R2. It can vary case by case. How to take instance to single user mode: Two situation can happen: 1. Standalone Instance  2. Failover Cluster Instance 1. Standalone Instance: Go to SQL Config Manager SQL Service Properties -> Advanced Properties -> Startup Parameters put -m; in the Startup Parameters Apply and Okay - it will ask to restart the SQL Service to take effect. Stop and Start the service from Config Manager or you can stop and serv...
Read more