MS SQL Tips

anim-bulbglow.gif (80393 bytes)

Acacia is happy to provide programming tips. Help others by adding your own tips to this page.


The tips shown here are for information only and should be tested thoroughly before use in any production environment.

Last updated 18:07:39-20/12/2008

Click here to add your own tips.

Click to search the Acacia site
Click to search using Google

MS SQL Tips

Microsoft SQL Server tips.

Tip Details
Add column or change column data type or size
Geoff
alter table tablename alter column columnname varchar(100) not null
Code Building Stored Procedure
Geoff
-- The following SP creates primary keys on a range of tables
CREATE PROCEDURE CreatePK
AS
DECLARE @ViewName varchar(255),
@sql nvarchar(1024),
@TabName varchar(255)
DECLARE name_cursor CURSOR FOR
Select Distinct TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME like 'xxxxx%'

OPEN name_cursor
FETCH NEXT FROM name_cursor INTO @ViewName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'ALTER TABLE [dbo].[' + @ViewName + '] WITH NOCHECK ADD CONSTRAINT [' + @ViewName + ' _PK] PRIMARY KEY CLUSTERED '
SET @sql = @sql +'([FIELDNAME]) ON [PRIMARY] '
PRINT @sql
EXEC sp_executesql @sql
FETCH NEXT FROM name_cursor INTO @ViewName
END
CLOSE name_cursor
DEALLOCATE name_cursor

GO
Compare RI in 2 databases
Luan
Compare referential integrity in a reference database (REF) with another CMP database.

SELECT
std.[table],
std.[constraint]
FROM
(SELECT
tbl.name [table],
o.name [constraint]
FROM
REF.dbo.sysobjects o
INNER JOIN REF.dbo.sysconstraints c ON c.constid = o.id
INNER JOIN REF.dbo.sysobjects tbl ON tbl.id = c.id
WHERE o.xtype = 'F') std
LEFT OUTER JOIN
(SELECT
tbl.name [table],
o.name [constraint]
FROM
CMP.dbo.sysobjects o
INNER JOIN CMP.dbo.sysconstraints c ON c.constid = o.id
INNER JOIN CMP.dbo.sysobjects tbl ON tbl.id = c.id
WHERE o.xtype = 'F') db_to_be_checked
ON db_to_be_checked.[table] = std.[table] AND db_to_be_checked.[constraint] = std.[constraint]
WHERE
db_to_be_checked.[table] IS NULL
ORDER BY
std.[table],
std.[constraint]
Copy SQL Diagrams
Geoff
In order to copy a SQL Server diagram from one database to another, you must copy records from the dtproperties table.  You can use the following script or DTS.

SET IDENTITY_INSERT dtproperties ON

INSERT dtproperties ([id],objectid,property,value,lvalue,version)

SELECT [id],objectid,property,value,lvalue,version
FROM SOURCEDB..dtproperties

SET IDENTITY_INSERT dtproperties OFF

Dates and Times
Geoff
You may have noticed that SQL Enterprise Manager can show datetime columns as just the date or just the time portion.

In order to display just dates, you should set the time portion to ’00:00:00.000’ or just leave it out when inserting/updating data.
In order to display just time, you must set the date portion to '30/12/1899'.  (I suspect that this magic date maps to an underlying -1 for the date portion).
If you want to set a datetime to just the time portion, the following SQL will do it…
set dateformat dmy
update xxxxxx
set start_time = '30/12/1899 ' + substring(convert(varchar,start_time),13,100)
where ….
Debug deadlocks
Geoff
Turn on tracing from command shell using:
DBCC TRACEON (3605,1204,-1)
Dynamic SQL
Geoff
declare @SQL nvarchar(4000)
set @SQL = 'update tbl set x = ' + @x + ' '
exec sp_executesql @SQL
Enter null into column
Geoff
To enter a null value into a column within enterprise manager, hit Ctrl-Shift-0 (thats zero).
Find a column
Geoff
-- find the pesky missing column
select t.name, c.name
from dbo.syscolumns c,
dbo.sysobjects t
where c.id= t.id
and c.name like '%parent_type%'
order by c.colorder
Is column primary key
Geoff
declare @is_pk int
set @is_pk = null
select @is_pk = count(*)
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS as tc join
INFORMATION_SCHEMA.KEY_COLUMN_USAGE as kcu on
kcu.CONSTRAINT_SCHEMA = tc.CONSTRAINT_SCHEMA
and kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
and kcu.TABLE_SCHEMA = tc.TABLE_SCHEMA
and kcu.TABLE_NAME = tc.TABLE_NAME
where kcu.TABLE_NAME = @tbl
and kcu.COLUMN_NAME = @column_name
and tc.CONSTRAINT_TYPE in ( 'PRIMARY KEY', 'UNIQUE' )
List columns
Geoff
select t.name, c.name
from dbo.syscolumns c inner join
dbo.sysobjects t on t.id = c.id
where c.name in ('table_name','asset_table')
List table columns
Geoff
select name from dbo.syscolumns
where id=
(
select id from dbo.sysobjects
where name='mytable'
)
order by colorder
List tables by key
Geoff
select name from dbo.sysobjects
where name like '%_files%'
and xtype = 'U'
order by name
OLAP
Geoff
If you haven't used it yet, you must look at the power that is provided by analysis services on SQL server.  It comes free and has great functionality.
OLAP Multi Dimension Expression (MDX)
Geoff
Sample MDX to slice and dice a cube (works)

with
-- Declare calculated column
member [Measures].[Variance] as ' [Planning Amt] - [Task Qty]'
member [Measures].[Rate per hr] as ' [Planning Amt] / [Planning Hrs]'

select
--- Column hierarchy
NON EMPTY crossjoin( { [Measures].[Planning Amt], [Measures].[Task Qty] ,[Variance], [Rate per hr] },
crossjoin( { [Planning Type].[Planning Type].[Baseline], [Planning Type].[Planning Type].[Actual], [Planning Type].[Planning Type].[Annual] },
{ [Status Code].[Status Code].[O] , [Status Code].[Status Code].[C] }
)) on columns,

--- Row hierarchy
NON EMPTY crossjoin({ [work_task_codes].[Task Code].members},
crossjoin ({ [Planning Asset Id].[Planning Asset Id].[1504]},
{ [Planning Date_Yr_Mth].[Year].members }
)) on rows
from works_planning

where
-- Slicer
(
[Regulated Flag].[All Regulated Flag].[Y] ,
[projects].[All projects].[010]
)
Rename column
Geoff
exec sp_rename 'oldcolname', 'newcolname' , 'COLUMN'
SQL Server all table count
Geoff
To count the records in all tables in a SQL Server database use the following:
select 'Owner'=convert(char(10),t.TABLE_SCHEMA),
'Table Name'=convert(char(25),t.TABLE_NAME),
'Record Count'=max(i.rows)
from sysindexes i, INFORMATION_SCHEMA.TABLES t
where t.TABLE_NAME = object_name(i.id)
and t.TABLE_TYPE = 'BASE TABLE'
group by t.TABLE_SCHEMA, t.TABLE_NAME
SQL Tutorial
Kevin Evans
Kevin Evans was kind enough to recommend http://www.intermedia.net/support/sql/sqltut.asp as a good SQL Tutorial.
Standard cursor block
Geoff
declare @col varchar(50),
@length int,
@colorder int,
@data_type int


DECLARE cursor_attrs CURSOR LOCAL FAST_FORWARD FOR
select name, length, xtype, colorder
from [dbo].[vw_tmpdfns]
order by colorder

OPEN cursor_attrs

-- Grab first record
FETCH NEXT FROM cursor_attrs INTO @col, @length, @datatype, @colorder

WHILE @@FETCH_STATUS = 0
BEGIN

-- Do processing
FETCH NEXT FROM cursor_attrs INTO @col, @length, @datatype, @colorder
END
Turn RI checks on/off
Geoff
Turn on RI for a table:
alter table tblname check constraint all

Turn RI off for a table:
alter table tblname nocheck constraint all

where tblname is replaced with the name of the table to change.
Wildcards
Geoff
If you wish to do a wildcard search and not have SQL treat underscores as single character wildcards, use the following syntax:

select *
from mytable
where mykeyfld like 'A[_]B%'

By enclosing the underscore in the square brackets, it is treated as a literal.

Click here to add your own tips.