CS & IT Solutions

If you’ve forgotten the sa password for your SQL server, you are probably in a panic. Fear not, for you can reset the password and once again have access to the powers of the sa account by using the query window in the Management Studio.

1. Open the SQL Server Management Studio.

2. Open a New Query.

3. Copy, paste, and execute the following:

GO
ALTER LOGIN [sa] WITH DEFAULT_DATABASE=[master]
GO
USE [master]
GO
ALTER LOGIN [sa] WITH PASSWORD=N’NewPassword’ MUST_CHANGE
GO

where NewPassword is the password you wish to use for the sa account.

You can now breathe easier, and once again access the sa account. Whew!

courtesy http://www.tech-recipes.com/rx/3766/sql-server-2005-how-to-reset-a-forgotten-password-for-the-sa-account/

Making changes to a database that has been created by a third pary can be a difficult task, especially when you don’t want to break any of the existing stored procedures. You can search the database’s stored procedures for keywords to find which procedures use a keyword that is involved in your change. This makes it much easier in identifying these procedures so you can make the appropriate changes.

1. Open the SQL Server Management Studio.

2. Go to the toolbar and click the New Query button.

3. Select the desired database.

4. Copy, paste and execute the following:

SELECT sys.sysobjects.name, sys.syscomments.text
FROM sys.sysobjects INNER JOIN syscomments
ON sys.sysobjects.id = sys.syscomments.id
WHERE sys.syscomments.text LIKE ‘%YourKeyword%’
AND sys.sysobjects.type = ‘P’
ORDER BY sys.sysobjects.NAME

Where YourKeyword is replaced with the value you are searching for.

The result set will contain the name and text of each stored procedure that contains the given keyword.

courtesy http://www.tech-recipes.com/rx/3701/sql-server-2005-locating-stored-procedures-that-contain-a-keyword/

If you have grown tired of having to view the splash screen each and every time you open the SQL Server Management Studio, you can use a simple command line switch in your shortcut to bypass the annoyance.

1. Right-click on the desktop.

2. Click New and select Shortcut.

3. Click the Browse button and go to the Sqlwb executable (”C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\Sqlwb.exe”).

4. After the full path and the end quotes, add one space and type in -nosplash.

5. Click Next.

6. Type a name for the shortcut.

7. Click Finish.

courtesy http://www.tech-recipes.com/rx/3353/sql-server-20052008-disable-management-studios-splash-screen/

A foreign key is a column or columns that are used to enforce a link between data in two tables. While SQL Server gives you no quick and easy way to view all foreign keys in a database, this quick query will give you that information. It comes in handy when trying to troubleshoot Foreign Key Constraint errors.

1. Open a New Query on the desired database.

2. Copy, paste, and execute the following:
SELECT
FKConstraintName = a.CONSTRAINT_NAME,
FKTable = b.TABLE_NAME,
FKColumn = c.COLUMN_NAME,
PKTable = d.TABLE_NAME,
PKColumn = e.COLUMN_NAME

FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS a
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS b
ON a.CONSTRAINT_NAME = b.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS d
ON a.UNIQUE_CONSTRAINT_NAME = d.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
ON a.CONSTRAINT_NAME = c.CONSTRAINT_NAME
INNER JOIN
(
SELECT
f.TABLE_NAME, g.COLUMN_NAME

FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS f
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE g
ON f.CONSTRAINT_NAME = g.CONSTRAINT_NAME
WHERE f.CONSTRAINT_TYPE = ‘PRIMARY KEY’
) e

ON e.TABLE_NAME = d.TABLE_NAME

ORDER BY a.CONSTRAINT_NAME

You can now easily see all foreign keys in that database.

courtesy http://www.tech-recipes.com/rx/3194/microsoft-sql-server-how-to-find-the-foreign-keys-in-a-database/