Acacia Tips - MS SQL

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)

dota

[*map/map_all_coml11.txt

dota

[*map/map_cnc2_11_mordy.txt

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 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]
)

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.

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

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.

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)
dota	[*map/map_all_coml11.txt
dota	[*map/map_cnc2_11_mordy.txt
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 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] )
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.
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
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.

MS SQL Tips