Scripts

Technical Article

sp_spaceused2

An update to sp_spaceused2. This update fixes some problems related to dbs that are non-accessible such being offline. This stored proc can be run from any database when compiled in master and can report information on all databases at once. Get information on all dbs, one db, one db and all its tables or one […]

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

2002-02-19

331 reads

Technical Article

Analyze Space Used for each Table

The following batchfile demonstrates how to use Windows command extension to extract the values of sp_spaceused for each table in the database. The output of sp_spaceused itself contains strings that do not get easily analyzed. The batch file extracts the numbers and outputs the sizes for each table in the database.Copy the batchfile to a […]

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

2002-02-18

2,325 reads

Technical Article

DATEMY - Covnerts and validates MM/YY, MM/YYYY

If you've ever needed a convenient and quick way to convert/validate what could possibly be a mm/yy or mm/yyyy date to mm/dd/yyyy (date of month is 01) check out this custom function.  Eg:  select dbo.datemy('04/02') outputs smalldatetime '2002-04-01 00:00:00'.  Select dbo.datemy('04/2002') outputs the same smalldatetime '2002-04-01 00:00:00'.

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

2002-02-15

936 reads

Technical Article

Rename User Login

You can effectively rename a user account but as this is messing wit the system tables I would suggest dropping and adding the user back. But for those of you who really want to know how here it is.First off I tested this and it works fine renaming a user. However this is directly modifying […]

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

2002-02-14

967 reads

Technical Article

Send notifications on job STEP failures (trigger)

Have you ever wanted to have SQL note a failure on a step, yet continue running the job.  I found it to be annoying that you have to fail (and end) a job in order to send a message to an operator.  I have processes that run at off times, I want to know when […]

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

(2)

You rated this post out of 5. Change rating

2002-02-13

2,410 reads

Technical Article

Compare Definition and Data of table in 2DBs

This proc compares the table definition and the data in a table which is in two of your databases. Typically you may have a development database and a production database on the same server and you just want to know that the table is the same on both. (This proc will express the data differences […]

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

2002-02-11

329 reads

Technical Article

Server User Access

This script runs from the master database and records all the databases on the server. It then checks each database for users and records their name and the database(s) on which they have access. You can then create a nice cross tab report using any report designer (I used crystal) showing users on the databases […]

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

2002-02-11

534 reads

Blogs

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...

What's New in SQL Server for the Developer: SQL Server 2012

By

Part 1 of my What's New in SQL Server for the Developer series. IIF,...

Read the latest Blogs

Forums

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...

Data Lost in the Cloud

By Steve Jones - SSC Editor

Comments posted to this topic are about the item Data Lost in the Cloud

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