Tag: SQL

How to delete/drop/select all the tables from SQL Server Database?

I stumbled on a thread at MSDN titled “How to delete/drop all the tables from SQL Server Database without using Enterprise Manager?” when trying to look for a way to drop all tables from a SQL server database.

Here are some of the nuggets from the thread:

1) How to drop all tables from the database

EXEC sp_MSforeachtable @command1 = “DROP TABLE ?”

2) How to delete all data from all the tables in the database

EXEC sp_MSforeachtable @command1 = “DELETE FROM ?”

3) How to truncate all data from all the tables in the database

EXEC sp_MSforeachtable @command1 = “TRUNCATE TABLE ?”

4) How to select all the rows from all the tables in the database

EXEC sp_MSforeachtable @command1 = “SELECT * FROM ?”

5) How to print the names and owners of all the tables in the database

EXEC sp_MSforeachtable @command1 = “PRINT ‘?'” –Just print the tables names with owner(dbo)

Some caveats, like referential integrity rules and foreign keys references will have to be kept in mind when attempting to truncate data. Also, some of the changes can not be rolled back.

[Microsoft][ODBC SQL Server Driver]Cannot generate SSPI context

Another error that came my way this morning was the SSPI context error when trying to connect to a SQL server.

A colleague was trying to use SQL Management Studio to connect to a specific database server. His Active Directory credentials had been setup with data reader privileges to the specific database. Each time he attempted to connect to the database he received the error message:

—————————
Microsoft SQL Server Login
—————————
Connection failed:
SQLState: ‘HY000’
SQL Server Error: 0
[Microsoft][ODBC SQL Server Driver]Cannot generate SSPI context

—————————
OK
—————————

After trying some suggestions on the Cannot generate SSPI context support page, I stumbled on a forum post that discussed checking how the specific account was setup on Active Directory. It turns out that his account had an option enabled that was set to ‘Use DES encryption types for this account’.

SSPI context

After disabling this option he was able to connect without any problems.

Configure SQL Server 2005 to Listen on a Specific TCP Port

To change the default port for SQL Server 2005 follow the steps listed below:

  1. Open SQL Server Configuration Manager which is found under Microsoft SQL Server 2005 > Configuration Tools
  2. In the SQL Server Configuration Manager window, expand SQL Server 2005 Network Configuration
  3. Click on Protocols for
  4. Right click on TCP/IP and select Properties
  5. In the TCP/IP Properties box, click on the IP Addresses tab
  6. If the TCP Dynamic Ports dialog box contains 0, indicating the Database Engine is listening on dynamic ports, delete the 0.
  7. In the TCP Port box, type the port number you wish this IP address to listen on, and then click OK
  8. Restart the SQL Server instance and the port changes will be applied to the respective instance.

For more information about Configuring a SQL Server to listen on a specific port visit How to: Configure a Server to Listen on a Specific TCP Port (SQL Server Configuration Manager)

Changing the Default Location of SQL 2005 Files

To change the default location of SQL 2005 files, follow the instructions below:

  1. Open SQL Server Management Studio
  2. Right click the server name and choose Properties
  3. On the Server Properties page, select Database Settings
  4. Under Database default locations type in the path to the respective locations for Data and Logs.

One thing I learned while trying to setup default paths is that SQL expects data files to be found in a child directory named Data. For more information about SQL Server 2005 paths visit File Locations for Default and Named Instances of SQL Server 2005.