Scripts

Technical Article

blocked  and blocker  spid and details

2 steps:step 1 => create   a table in master database called BlockCheckstep 2 =>  create the stored procedure in master  called  BLOCK_CHECK @database_name varchar(20)What SP  does:-    it gets the  spid that is blocked  + info about it (like what it does  at the moment  it is blocked)-    it gets the spid  that  acts as a […]

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

(1)

You rated this post out of 5. Change rating

2003-06-05

768 reads

Technical Article

Improving performance on joins to large tables.

In your tenure as a DBA/developer, you've probably been asked to write dozens of stored procedures similar to this:CREATE PROCEDURE ContactInfo@ContactID intASSELECT c.Name, a.Address, p.PhoneFROM Contact cLEFT JOIN ContactAddress aON c.ContactID = a.ContactIDLEFT JOIN ContactPhone pON c.ContactID = p.ContactIDWHERE c.ContactID = @ContactIDBut there is a more efficient way to write such a query.....particularly if you […]

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

(1)

You rated this post out of 5. Change rating

2003-06-03

1,450 reads

Technical Article

Find Duplicate Employees, Customers, Etc.

Customers, employees, etc. come and go.  They get entered multiple times in inconsistent formats in databases.This simple script finds the duplicates across multiple formats and is easily modified as needed.  It's so easy, my grandmother could do it. Oops! No offense Grandma.For very large databases, create and join two temp tables indexed on the namedatetring […]

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

2003-05-30

254 reads

Technical Article

Show all User-Defined Datatypes for all Databases

This procedure displays all user-defined datatypes in all databases on a server.  Another example of the power of dynamic T-SQL, this procedure dynamically generates SELECT statements for the systypes tables in each database and UNIONs them together so that they display in one recordset.

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

2003-05-30

196 reads

Technical Article

Script to determine if all chars are same in field

This script is a fix to the "Script to determine if all chars are same in field"posted by Harvard Kinkead (hneal_98) . That script doesn't handle strings with trailing spaces (e.g. set @repeat = 'bbbb ')It happens because of the nature of Len function. To bypass this limitation my script concatenates one character to tested […]

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

2003-05-29

122 reads

Blogs

Should You Use CHOOSE(): #SQLNewBlogger

By

I was experimenting with CHOOSE() recently after I was asked a question. In a...

T-SQL Tuesday #204 – Are blogs still relevant? (Roundup)

By

I did not expect 27 responses to my T-SQL Tuesday #204 invitation, but it...

Build vs Buy AI: When to adopt, partner, build or buy

By

Should you adopt an off-the-shelf AI tool, partner with a specialist, build in-house, or...

Read the latest Blogs

Forums

Weird problem altering logins with sysadmin account

By Brandie Tarvin

Has anyone ever seen this before? I have sysadmin permission on a server. I...

Switching Schema II

By Steve Jones - SSC Editor

Comments posted to this topic are about the item Switching Schema II

A Practical Approach to Automating SQL Server Email Notifications

By Kunal Rathi

Comments posted to this topic are about the item A Practical Approach to Automating...

Visit the forum

Question of the Day

Switching Schema II

I run this query:

SELECT 
 s.name AS schemaname
 , o.name AS objectname
 FROM sys.objects o
 INNER JOIN sys.schemas s ON s.schema_id = o.schema_id
 WHERE o.name = 'Beer'
I get two rows back for the objects, named dbo.beer and etl.beer. Both the tables have identical schemas and are not partitioned. There are 25 rows in dbo.beer and 0 rows in etl.beer. I run this code:
ALTER TABLE dbo.beer switch TO etl.beer
What happens?

See possible answers