SQL

T-Sql and Sql Server related tips and tricks

SQL Server - Rebuilding Indexes

Goal: Rebuild indexes in SQL server. This can be done one at a time or with the example script below to rebuild all index for a specified table or for all tables in a given database. Why? The data in indexes gets fragmented over time. That means that as the index grows, the newly added rows to the index are physically stored in other sections of the allocated database storage space. Kind of like when you load your Christmas shopping into the trunk of your car and it is full you continue to load some...
  • Share This Post:
  • Share on Twitter
  • Share on Facebook
  • Share on Technorati
Posted On Monday, November 28, 2011 8:34 AM | Feedback (1)

T-Sql - rename a table or column name via stored procedure

Goal: Rename a database table or column name using a t-sql stored procedure. How to: Rename a table: EXEC sp_rename 'TableToBeRename', 'NewTableName' Rename a column: EXEC sp_rename @objname = 'TableNameColumnToBeRenamed, @newname = 'NewColumnName', @objtype = 'COLUMN'...
  • Share This Post:
  • Share on Twitter
  • Share on Facebook
  • Share on Technorati
Posted On Monday, October 24, 2011 1:06 PM | Feedback (1)

After restoring a SQL Server database from another server - get login fails

Issue: After you have restored a sql server database from another server, lets say from production to a Q/A environment, you get the "Login Fails" message for your service account. Reason: User logon information is stored in the syslogins table in the master database. By changing servers, or by altering this information by rebuilding or restoring an old version of the master database, the information may be different from when the user database dump was created. If logons do not exist for the users,...
  • Share This Post:
  • Share on Twitter
  • Share on Facebook
  • Share on Technorati
Posted On Tuesday, December 14, 2010 10:54 AM | Feedback (0)

How to find your table within a server

Goal: How to find a table on your server: SET NOCOUNT ON CREATE TABLE myTable99(TABLE_CATALOG sysname, TABLE_SCHEMA sysname, TABLE_NAME sysname, TABLE_TYPE varchar(50)) GO CREATE PROC usp_FindMyTable (@TABLE_NAME sysname = null) AS SET NOCOUNT ON TRUNCATE TABLE myTable99 IF @TABLE_NAME IS NULL BEGIN PRINT 'No Table to look for. Please supply a tabke name. Like: ' + CHAR(13) + ' EXEC usp_FindMyTable Orders' GOTO usp_FindMyTable_Exit END DECLARE @MAX_dbname sysname, @dbname sysname, @sql varchar(8000)...
  • Share This Post:
  • Share on Twitter
  • Share on Facebook
  • Share on Technorati
Posted On Tuesday, March 02, 2010 6:18 PM | Feedback (1)

How to stop a run-away SQL transaction

The Problem: Recently was testing my web-app against a SQL-Server 2008 database and got a time-out expired message from the SQL database. No matter what I did after that it just did not want to drop the transaction and I continually got the time-out. I needed to find and stop the SQL transaction. The solution: Sign onto the SQL-Server with the Enterprise Manager. Execute sp_who2 and look for rows that are blocked on another connection (the BlkBy column will have the spid of the blocking connection...
  • Share This Post:
  • Share on Twitter
  • Share on Facebook
  • Share on Technorati
Posted On Thursday, July 02, 2009 11:05 AM | Feedback (0)

Twitter