Deleting tables with dependancies

The best thing to do it is “Generate scripts for Drop”

Select Database -> Right Click -> Tasks -> Generate Scripts – will open wizard for generating scripts

Select the database -> next -> Set option ‘Script to create’ to true (want to create) -> Set option ‘Script to Drop’ to true (want to drop) -> Set option ‘Generate script for dependent object’ to true -> Next -> Select the Check box to select objects wish to create script -> Select the choice to write script (File, New window, Clipboard)

Using aspnet_regsql.exe

The aspnet_regsql.exe tool is installed along with the .NET framework and can be found at the following location:

32 Bit
C:\Windows\Microsoft.NET\Framework\[.NET verision]

64 Bit
C:\Windows\Microsoft.NET\Framework64\[.NET verision]

This tool will create all the necessary tablets for membership, role management, profile, Web Parts personalization, and health monitoring features.

Run the application and clicked next. You will then be prompted to configure or remove SQL Server for application services. For a new install select configure and click next. The next step will prompt you for the SQL Server login details, fill this in and click next. Your will then see the summary page, if everything is correct click next. Finally click finish to complete the configuration.

The following tables will be created in the database:

aspnet_Users
aspnet_Roles
aspnet_Profile
aspnet_PersonalizationPerUser
aspnet_PersonalizationAllUsers
aspnet_Paths
aspnet_Membership
aspnet_WebEvent_Events
aspnet_UsersInRoles
aspnet_SchemaVersions
aspnet_Applications

SQL Server – Copy a table into new table with/without data

Copy only the structure of an existing table into new table:

SELECT * INTO tblNew FROM tblOld WHERE 1=2

The above query will copy the structure of  an existing table(tblOld) into the new table(tblNew).

Copy only the structure with data of an existing table into new table:

SELECT * INTO tblNew FROM tblOld

This is also same like the previous query, but it copies the structure of existing table(tblOld) with data as well into the new table(tblNew).

SQL Server – Running large scripts and avoid out of memory errors

When trying to execute large scripts using SQL Sever Management Studio which are 10s – 100s of megabytes in size you may receive the following error:

Insufficent memory of continue execution of the program (mscorlib.dll)

Microsoft have provided a handy DOS tool which executes large sql scripts without causes memory issues called osql.exe. This lives in the following directory:

C:Program FilesMicrosoft SQL Server110ToolsBin

The 110 directory will differ depending on the version of SQL Sever Management Studio, this this case it's 2012. To create a database called MYDATABASE on SQL Server WINDOWS8SQLEXPRESS using a script stored in c:SQLDatabasesmydatabasescript.sql enter the following.

C:Program FilesMicrosoft SQL Server110ToolsBinn>osql.exe -S WINDOWS8SQLEXPRESS -d MYDATABASE -E -i c:SQLDatabasesmydatabasescript.sql