Scott on Writing

Feedback

# re: Neat Little SQL Server 2000 Enterprise Manager Tip 12/10/2004 1:49 PM Scott

See, I blew away my Windows profile at some point and, for some reason, I lost my SQL EM MMC plugin. I re-added it to the SQL EM but I had to save it as an .msc file. To make a confusing story short, I end up using Query Analyzer to do most of my DB work.

You can browse the DB objects in the left hand pane, right click on them, select "script to new window as...", and select from a variety of scripting options "Create", "Alter" , "Execute", etc... Depending on the type of DB object you click on.

It's helped me remember my T-SQL syntax and ends up being quicker in the long run than clicking around in SQL EM.

# re: Neat Little SQL Server 2000 Enterprise Manager Tip 12/10/2004 2:32 PM Scott Elkin

That is like the greatest tip ever. I just love little things like that!

# re: Neat Little SQL Server 2000 Enterprise Manager Tip 12/10/2004 3:20 PM David

You can drag & drop your table into SQL Query Analyzer as well, it will do the same.

# Neat Little SQL Server 2000 Enterprise Manager Tip (from: Scott Mitchell) 12/10/2004 8:42 PM TOURNEY LOGIC LINK BLOG

# Copy/Paste DDL From SQL Server Ent Mgr 12/12/2004 1:21 AM protected virtual void jayBlog {

# SQL Enterprise Manager Copy-paste tip from Scott Mitchell 12/13/2004 7:22 AM Dave Burke's Blog

# SQL Enterprise Manager Copy-paste tip from Scott Mitchell 12/13/2004 7:23 AM Dave Burke's Blog

# re: Neat Little SQL Server 2000 Enterprise Manager Tip 12/13/2004 10:31 AM Aaron Meis

You can also select many objects (tables, etC), but beware the resource meter! Might take a few minutes, but you can copy many items at one time. :)Thanks for the tip Scott.

# re: Neat Little SQL Server 2000 Enterprise Manager Tip 12/15/2004 8:27 AM Hilton Giesenow

Kewl tip! It works for other objects as well (SPs, etc.). Thanks for this.

# SQL Server 2000 Enterprise Manager Tip 12/19/2004 6:35 PM Patrick Steele's .NET Blog

# re: Neat Little SQL Server 2000 Enterprise Manager Tip 12/19/2004 7:24 PM foobar

I love you. You have made my week. Seriously. This is one of the greatest time savers ever.

# scripting the datas 12/19/2004 11:33 PM kerberoz

what about making a script of all the datas of the table? how we can do that easily?

# re: Neat Little SQL Server 2000 Enterprise Manager Tip 12/20/2004 8:54 AM Scott Mitchell

kerberoz, you can use the General SQL Script task in SQL Enterprise Manager to script all tables, sprocs, views, UDFs, etc. In SQL Server 2000, go to the Tools menu and there's an option titled "Generate SQL Script..."

hth!

# re: Neat Little SQL Server 2000 Enterprise Manager Tip 12/26/2004 1:46 AM kerberoz

thanks scott for the reply, but what i meant for my post is that scripting the actual data, i.e. a script with a several insert into tablename(...)

TIA!

# re: Neat Little SQL Server 2000 Enterprise Manager Tip 12/31/2004 7:31 AM Scott Mitchell

kerberoz, there are tools that provide the functionality that you need. For example, see Red-Gate software's SQL tools - http://www.red-gate.com/sql/summary.htm

hth

# re: Neat Little SQL Server 2000 Enterprise Manager Tip 1/5/2005 3:02 AM manish

Good I also discovered this option while working gon one of my projects. Its cool !! Anyways I have even experienced the transfer of MS Access tables to SQL Server easily. Can you post something about indexes in detail .

Regards,
manish

# re: Neat Little SQL Server 2000 Enterprise Manager Tip 5/5/2005 6:06 AM Brian Boyce

kerberoz
Here is a stored proc that should give you a starter.

Stored Procedure to Create Insert commands from table of data
At the moment dates need to be edited.

CREATE PROCEDURE usp_CreateInsert (@objname sysname, @includeID bit)
AS
-- Currently the proc ignores Identity columns (by checking syscolumns.status & 128 (0x80)
SET NOCOUNT ON

DECLARE @first bit,
@objid int,
@colid int,
@quote varchar(4),
@colname sysname,
@txt varchar(250),
@select varchar(8000),
@sqltxt varchar(8000)

SET @quote = ''''

SELECT @objid = id
FROM sysobjects
WHERE name = @objname

CREATE TABLE #sphelptab
(
col_name char (30) NULL,
col_type char (30) NULL,
col_type_val int NULL,
col_len int NULL,
col_prec char (5) NULL,
col_scale char (5) NULL,
col_status int NULL,
colid int NULL
)
INSERT
INTO #sphelptab
SELECT c.name,
t.name,
c.type,
c.length,
CONVERT(CHAR(5),c.prec),
CONVERT(CHAR(5),c.scale),
c.status,
c.colid
FROM syscolumns c,
systypes t
WHERE c.id = @objid
AND c.usertype *= t.usertype

--
-- Don't display precision and scale for datatypes
-- which they not applicable.
--

UPDATE #sphelptab
SET col_prec = '',
col_scale = ''
WHERE col_type IN
(SELECT name FROM systypes WHERE type NOT IN
(38,48,52,55,56,59,60,62,63,106,108,109,110,122))
--
-- Insert script
--
SELECT @sqltxt = 'INSERT [' + @objname + '] ('

if (@includeID = 0) begin
SELECT @colid = MIN(colid),
@first = 1
FROM #sphelptab
WHERE col_status & 128 <> 128
end
else begin
SELECT @colid = MIN(colid),
@first = 1
FROM #sphelptab
end

SET @select = 'SELECT '
WHILE (@colid IS NOT NULL) BEGIN

SELECT @colname= RTRIM(col_name),
@txt = CASE
WHEN col_type_val IN (37,39,47) THEN
' ISNULL(' + @quote+@quote+@quote+@quote +'+'+ RTRIM(col_name) +'+'+

@quote+@quote+@quote+@quote + ',''NULL'')'
ELSE ' ISNULL(CAST('+RTRIM(col_name)+ ' AS VARCHAR),''NULL'')'
END
FROM #sphelptab
WHERE colid = @colid

IF @first = 0
SELECT @sqltxt = @sqltxt + ', ' + @colname,
@select = @select + '+' + @quote + ',' + @quote + '+' + @txt
ELSE
SELECT @sqltxt = @sqltxt + ' ' + @colname,
@select = @txt

SELECT @colid = MIN(colid),
@first = 0
FROM #sphelptab
WHERE col_status & 128 <> 128
AND colid > @colid

END

SELECT @sqltxt = @sqltxt + ') VALUES (' + @quote + '+',
@select = @select + '+' + @quote + ')' + @quote + ' FROM [' + @objname + ']'

if (@includeID = 1) begin
select 'SET IDENTITY_INSERT [' + @objname + '] ON'
select 'GO'
end

EXEC( 'SELECT ' + @quote + @sqltxt + @select)

if (@includeID = 1) begin
select 'SET IDENTITY_INSERT [' + @objname + '] OFF'
select 'GO'
end

GO

# re: Neat Little SQL Server 2000 Enterprise Manager Tip 7/6/2005 11:55 AM neonprimetime

Excellent tip ... found it with a google search, and it really helped ... ty

# re: Neat Little SQL Server 2000 Enterprise Manager Tip 7/7/2005 7:55 AM Madhu

This is a fantastic tip.

# re: Neat Little SQL Server 2000 Enterprise Manager Tip 7/25/2005 6:44 PM Joe Rocker

Awesome tip. It wasn't what I was looking for, but still way cool.

# re: Neat Little SQL Server 2000 Enterprise Manager Tip 1/24/2006 2:37 AM Ari

Not very impressed by this functionality :) I would rather that Enterprise manager acted more like Windows explorer, i.e. copy/paste of a table would create a new table named "copy of..." containing the same data and constraints as the table being copied.

Thanks for your blog!

Title:
Name:
Url:
Enter the code you see:
Comments
Remember Me?