Scripts

Technical Article

Rename Foreign Key Constraints

This script will rename all FK constraint names in your database.  It will give them a user friendly name that can be used in error messages, etc. The FK contraint names will be formatted as FK_ChildTableName_ParentTableName_soid. Note: You first need to enable 'Allow modifications to be made directly to the system catalogs'. This can be […]

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

2003-09-19

1,226 reads

Technical Article

Know all Database sizes in a server.

We can know how much space a database is occupied in hard disk by using sp_spaceused function. If we want to find all database sizes at a time, we have to provide use  and sp_spaceused for all databases. It takes some time to write all those T-sql statments. My script will find each and every […]

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

(2)

You rated this post out of 5. Change rating

2003-09-16

1,690 reads

Technical Article

A faster way to count rows in a table

SQL Server includes the COUNT function for counting a table's rows - however, it can be slow.  Although querying the "rows" column from the sysindexes table is faster, it is not always accurate (e.g., if a bulk load process has recently taken place).  In addition, you would not be able to use this method in […]

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

(3)

You rated this post out of 5. Change rating

2003-09-11

476 reads

Technical Article

Get contents of a directory/share to a table

Following on from todays QOD (on xp_cmdshell permissions) I thought this might be useful.Takes a directory or UNC path and returns the contents as a table.  eg:exec sp_getdir '\\mypc\c$'returns:datestring timestring directory   filesize    nameoffile---------- ---------- ----------- ----------- ----------------------18/07/2003 10:45      1           NULL        Documents and Settings18/07/2003 11:02    […]

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

2003-09-04

308 reads

Technical Article

To Get Latest SQL Job Status

This query will give you the current job status of all scheduled jobs on sql server. Just by using sysJobs, and sysJobHistory table you will get to know the details about the last status of the scheduled jobs, irrespective of their status like failed, successful, or cancelled.Note: You must have admin right on MSDB database […]

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

(13)

You rated this post out of 5. Change rating

2003-09-04

8,604 reads

Technical Article

Convert float to duration-time string

This UDF is using in reporting where lots of durations are calculated, stored and then summed up and averaged for reports.  By storing the durations as floats (which are similar to the fixed point numbers datetimes are stored as) math functions are simpler.The problem is that float-times are not human readable, and cast(@floatime as datetime) […]

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

2003-09-03

1,040 reads

Technical Article

Execute large sql (>8000 chars)

With a dynamic sql script under 4000 chars use sp_executesql With under 8000 chars write it to a variable.For anything longer you can use this script.  Use UpdateText and WriteText to write your dynamic sql to a text/ntext field in a table somewhere and then pass its location to this script.

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

2003-09-01

645 reads

Blogs

Dazzling Stuff in Databricks (SQL) – Qualify

By

Once upon a time I had a series about some cool stuff in Snowflake:...

Advice I Like: Overnight Success

By

“Most overnight successes – in fact any significant successes – take at least 5...

A CLR Assembly Broke My Availability Group

By

Sounds scary! Did I get your attention?

Read the latest Blogs

Forums

Why Successful SQL Server Backups Are Not Enough

By Almighty

Comments posted to this topic are about the item Why Successful SQL Server Backups...

Adding Bits II

By Steve Jones - SSC Editor

Comments posted to this topic are about the item Adding Bits II

An Unlimited Token Budget

By Steve Jones - SSC Editor

Comments posted to this topic are about the item An Unlimited Token Budget

Visit the forum

Question of the Day

Adding Bits II

I have a table, dbo.bittest, where I am testing an application response. This has this structure:

CREATE TABLE [dbo].[BitTest]
(
[insertdate] [datetime] NULL CONSTRAINT [DF__BitTest__insertd__300F11AC] DEFAULT (getdate()),
[a] [bit] NULL
) ON [PRIMARY]
GO
My application runs this code:
INSERT dbo.BitTest
(
    insertdate,
    a
)
VALUES
(DEFAULT, true)
GO

See possible answers