Twitter

Follow SQLQuill on Twitter

Subscribe

MN Pass

How To: Set the Default Scripting Options in SQL Server Management Studio

I just spent 10 minutes looking for this, it’s very simple and fairly obvious.  I’m posting it so I don’t forget it and hopefully it may help others.

I’ve got to script out all tables, indexes, primary keys, foreign keys, defaults and the collation of all tables in all of our databases.  They all have to be the same style and the options I have to choose are slightly different than the defaults.  To make fast work of this, I’ll change the defaults to be exactly what I want then from my Central Management Server, I script each and every database I need.

Here’s how to set the defaults:

In SQL Server Management Studio, go to Tools –> Options.  Expand the SQL Server Object Explorer list and select Scripting.  Change what you want, close this window and Ta-Da the next time you generate scripts, the defaults are set.

 Tools --> Options

October 22nd, 2010 | Tags: , | Category: HowTo, SQL2008 | Leave a comment

How To: Move Central Management Server Groups and Servers from one server to another

Here’s a script I came up with to move my central management server’s Groups and Server from one server to another.  I know it doesn’t make a ton of sense to do this, but I started testing the CMS setup on a test server and it’s worked so well, we wanted to put it on a production server.  There are two scripts that are run on the source server that create scripts to run on the destination server.

/*
--Verify you've got the correct groups.
Select * from dbo.sysmanagement_shared_server_groups_internal
where is_system_object = 0 --ignore the builtin groups.
*/
Select '
Declare @server_group_id int
EXEC msdb.dbo.sp_sysmanagement_add_shared_server_group @parent_id=1, @name=N''' + name
+ ''', @description=N''' + description + ''', @server_type=0, @server_group_id=@server_group_id OUTPUT'
+ CHAR(13) + CHAR(10)
+ 'Go'
from dbo.sysmanagement_shared_server_groups_internal
where is_system_object = 0 --ignore the builtin groups.

Again, run the output of this on the destination server.

Here’s the script to move the servers with their groups – note the group id is dynamic, so even if the destination server has more groups, it will figure out the correct one.

Select
'Declare @SGID int, @SGName nvarchar(255)
Set @SGName = ''' + SGI.name + '''
Select @SGID = server_group_id from dbo.sysmanagement_shared_server_groups_internal
where name = @SGName
Declare @server_id int
EXEC msdb.dbo.sp_sysmanagement_add_shared_registered_server @server_group_id=@SGID, @name=N''' +
RSI.name + ''', @server_name=N''' + RSI.server_name + ''', @description=N''' + RSI.description + ''', @server_type=0, @server_id=@server_id OUTPUT'
+ CHAR(13) + CHAR(10)
+ 'Go'
from dbo.sysmanagement_shared_registered_servers_internal RSI
Join dbo.sysmanagement_shared_server_groups_internal SGI on RSI.server_group_id = SGI.server_group_id

Finally, run the output of this on the destination server.

Here’s a couple of resources I used to figure this stuff out:

October 7th, 2010 | Tags: , | Category: HowTo, Scripts | Leave a comment

Kids these days…(T-SQL Tuesday #007)

TSQL2sDay150x150

The SQLChicken, Jorge Segarra, (blog/twitter) is hosting this months T-SQL Tuesday.  It’s called “T-SQL Tuesday #007: Summertime in the SQL”.   The question is “What’s your favorite hot new feature in the R2 (I’ll be nice and include 2008 in general) release?”

Well, the answer to the question is simple – the merge statement…and IntelliSense…and the resource governor…and compression both for data and backups.  Okay, there are lots of good changes in SQL 2008 and I’ve seen lots of changes in my day.

In my old guy voice I say, “Young DBA’s these days, don’t know what it’s all about.  Things are easy, point and click, drag and drop, right click, create script, etc….”

Back in my day…when we want to move data, we would have to create update statements, insert statements (usually based on a outer join where the joining column was null) and delete statements.  There wasn’t any such thing as a merge statement.  And we used to have to type these things out by hand, or copy and paste them from a result set from sysobjects – there was no IntelliSense.  The way I used do it was to create temp tables, stage the data usually by creating a script in Excel and copying it for all the rows in the table.  We had to write out Insert #TempTable1 Values (<A1>, <A2>…) then on the next line it would be Insert #TempTable1 Values (<B1>, <B2>…).  There was no Insert #TempTable1 Values (<A1>, <A2>…),  (<B1>, <B2>…).  I usually had my standard of #TempTable1 would be updates, #TempTable2 would be inserts and #TempTable3 would be deletes.  There wasn’t any table variables either, so we always had to drop the temp tables – or TempDB might fill up during maintenance. 

Back in those days, there was no auto file grow either.  There better be enough room in the database files for everything to fully complete or things were going to fill up and be left in an incomplete state. 

There was no resource governor either, we scheduled index rebuilds, data imports, data cleanup tasks for off hours – when the systems weren’t expected to be online.

Back in the early days, there were no DDL triggers either, there was no SOX, so there wasn’t as much of a need to have DDL triggers.  If someone needed a change in production, the green play button on the procedure you were looking at would change it.  There wasn’t a “Generate Change Button” for tables and columns either, we did it the same way we did it in the development environment – by hand via the GUI. 

Even creating databases, we had to create the devices first, then the database and we had to back them up to backup devices (that actually made restores simpler as the file name was always the same).  There was no compression built into SQL.  For large backups, we used 3rd party tools and then we’d include the tool in the backup directory and put it to tape.  If we ever had to restore the backups, we would have the tool on the tape as well – who knows how many licensing agreements we broke by doing that.

When trouble shooting issues, we used to have keep hitting the refresh button in activity monitor to see if anything had changed – there was no auto refresh.  There were no filters in it either, so we had to look through all the connections to the server. 

If we found a performance issue, we didn’t have an execution plan to go off of, much less SQL didn’t tell us what indexes were missing!  I always used the “Set Statistics IO” option and looked at the logical reads.

Things have changed a lot since SQL 6.5, but the bottom line is that a table is still a table, a column is still a column and an index is still an index (although those are changing)

June 8th, 2010 | Tags: , | Category: TSQL Challenge, tsql2sday | 3 comments

SQL 2008 System Generated Names for Defaults

I had to create a script for dropping constraints on a table – no problem if the constaint is a named default, but if the default has a system generated name, it gets a bit hairy.   Here’s an example of how the defaults get created:

Create table TestTable1
	(TestConst datetime default Getdate())
Go

I googled this and found Pinal Dave had a good script to find the constraints in database located here. 
Here’s what I came up with for a script to find these constraints:

Declare @TableName varchar(500),
	@ColumnName varchar(500),
	@DFName varchar(500),
	@SchemaName varchar(500),
	@SQL varchar(2000)

--Change these for the table and column you are looking for--
Set @TableName = 'TestTable1'
Set @ColumnName = 'TestConst'

SELECT OBJECT_NAME(SO.OBJECT_ID) AS NameofConstraint,
	SCHEMA_NAME(SO.schema_id) AS SchemaName,
	OBJECT_NAME(SO.parent_object_id) AS TableName,
	SC.name as ColumnName,
	SO.type_desc AS ConstraintType
FROM sys.objects SO
join sys.columns SC on SC.default_object_id = SO.object_id
WHERE SO.type_desc LIKE '%CONSTRAINT'
and SC.object_id = object_id(@TableName)
and SC.name = @ColumnName

Finally, here’s the script to show and drop it:

Declare @TableName varchar(500),
	@ColumnName varchar(500),
	@DFName varchar(500),
	@SchemaName varchar(500),
	@SQL varchar(2000)

--Change these for the table and column you are looking for--
Set @TableName = 'TestTable1'
Set @ColumnName = 'TestConst'

SELECT OBJECT_NAME(SO.OBJECT_ID) AS NameofConstraint,
	SCHEMA_NAME(SO.schema_id) AS SchemaName,
	OBJECT_NAME(SO.parent_object_id) AS TableName,
	SC.name as ColumnName,
	SO.type_desc AS ConstraintType
FROM sys.objects SO
join sys.columns SC on SC.default_object_id = SO.object_id
WHERE SO.type_desc LIKE '%CONSTRAINT'
and SC.object_id = object_id(@TableName)
and SC.name = @ColumnName

If exists (Select 1 from sys.objects SO join sys.columns SC on SC.default_object_id = SO.object_id
		where SC.object_id = object_id(@TableName)
		and SC.name = @ColumnName)
Begin
	Select @DFName = SO.name,
		@SchemaName = SCHEMA_NAME(SO.schema_id)
	from sys.objects SO join sys.columns SC on SC.default_object_id = SO.object_id
	where SC.object_id = object_id(@TableName)
	and SC.name = @ColumnName

	Select @SQL = 'ALTER TABLE ' + @SchemaName + '.' + @TableName + ' DROP CONSTRAINT ' + @DFName
	EXEC (@SQL)
End
March 11th, 2010 | Tags: , , | Category: Scripts, SQL2008 | Leave a comment

SQL 2008 Certified

I took my 70-453 test, Upgrade: Transition Your MCITP SQL Server 2005 DBA to MCITP SQL Server 2008, yesterday and passed, so it’s official:

February 24th, 2010 | Tags: , , | Category: SQL2008 | Leave a comment