What is the correct syntax to add an index to a table?
Example: tableA with fieldB- create an index on fieldBCheck out CREATE INDEX (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create_64l4.asp).
-PatP|||Originally posted by Pat Phelan
Check out CREATE INDEX (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create_64l4.asp).
-PatP
Thank you! I found it immediatley after I submitted the post|||Does anyone know the correct procedure to setup a pull relationship from MS Access to a SQL database and vice versa?|||Refer to your post http://www.dbforums.com/t994687.html
Showing posts with label table. Show all posts
Showing posts with label table. Show all posts
Friday, March 30, 2012
Scripting ALTER TABLE
I need to create a script to disable all triggers and constraints in my
database.
database.
It appears as though I cannot use a local variable for the table name in the
ALTER TABLE statement (e.g. ALTER TABLE @.TBL).
Is there any reason for this?
Thanks,
Kevin"Kevin Haugen" <khaugen@.pacbell.net> wrote in message
news:jx1Hd.12736$5R.1377@.newssvr21.news.prodigy.co m...
>I need to create a script to disable all triggers and constraints in my
>database.
> It appears as though I cannot use a local variable for the table name in
> the ALTER TABLE statement (e.g. ALTER TABLE @.TBL).
> Is there any reason for this?
> Thanks,
> Kevin
You can't use variables in place of table or column names, except when using
dynamic SQL, which has its own issues. Although if you're a DBA running an
admin script, then it's usually a reasonable option - there's more
discussion here:
http://www.sommarskog.se/dynamic_sql.html
Unfortunately, you don't say what your goal is, but if it's to load data
into the database, then all the usual loading tools (bcp.exe, DTS, BULK
INSERT) can ignore both constraints and triggers, so you might not need a
script anyway.
Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message
news:41ecd751$1_1@.news.bluewin.ch...
> "Kevin Haugen" <khaugen@.pacbell.net> wrote in message
> news:jx1Hd.12736$5R.1377@.newssvr21.news.prodigy.co m...
>>I need to create a script to disable all triggers and constraints in my
>>database.
>>
>> It appears as though I cannot use a local variable for the table name in
>> the ALTER TABLE statement (e.g. ALTER TABLE @.TBL).
>>
>> Is there any reason for this?
>>
>> Thanks,
>>
>> Kevin
>>
> You can't use variables in place of table or column names, except when
> using dynamic SQL, which has its own issues. Although if you're a DBA
> running an admin script, then it's usually a reasonable option - there's
> more discussion here:
> http://www.sommarskog.se/dynamic_sql.html
> Unfortunately, you don't say what your goal is, but if it's to load data
> into the database, then all the usual loading tools (bcp.exe, DTS, BULK
> INSERT) can ignore both constraints and triggers, so you might not need a
> script anyway.
> Simon
I'll look into it. I'm planning on converting existing data into a new
format. Since I have to identify each table and write a query to do the
conversion, I could easily do a copy/paste for each table to disable and
re-enable the triggers and constraints. I am hoping to shortcut some of the
work by automating that piece.
Thanks,
Kevin
Wednesday, March 28, 2012
Script, Save, Export SQL Database Diagrams
When you create database diagrams in Enterprise Manager, the details
for constructing those diagrams is saved into the dtproperties table.
This table includes an image field which contains most of the relevant
infomation, in a binary format.
for constructing those diagrams is saved into the dtproperties table.
This table includes an image field which contains most of the relevant
infomation, in a binary format.
SQL Enterprise manager offers no way to script out those diagrams, so
I have created two Transact SQL components, one User Function and one
User Procedure, which together provide a means to script out the
contents of the dtproperties table, including all of the binary based
image data, into a self documenting, easy to read script. This script
can be stowed away safely, perhaps within your versioning software,
and it can subsequently be recalled and executed to reconstruct all
the original diagrams.
The script is intelligent enough not to overwrite existing diagrams,
although it does allow the user to purge any existing diagrams, if
they so choose.
Once these two objects have been added to any database, you may then
backup (script out) the current database diagrams by executing the
stored procedure, like this:
Exec usp_ScriptDatabaseDiagrams
By default, all database diagrams will be scripted, however, if you
want to script the diagrams individually, you can execute the same
procedure, passing in the name of a specific diagram. For example:
Exec usp_ScriptDatabaseDiagrams 'Users Alerts'
The Transact SQL code for the two objects is too long to paste here,
but if you are interested, I will email it to you. Just drop me a note
at: clayTAKE_THIS_OUT@.beattyhomeTAKE_THIS_OUT.com (Remove both
instances of TAKE_THIS_OUT from my email address first!!)
-ClayOk, I've had a few emails on this, so I'll post the code here.
This is the code for the first component, a user defined function to
translate a Varbinary value into a Varchar string of hex values. The
hex string will obviously contain twice as many bytes as the binary
string.
The formatting of the code pasted here got a little messed up with the
line wraps, but you should be able to clean that up easily enough in
SQL Query Analyzer.
-Clay
if exists (select 1
from sysobjects
where name = 'ufn_VarbinaryToVarcharHex'
and type = 'FN')
drop function ufn_VarbinaryToVarcharHex
GO
CREATE FUNCTION dbo.ufn_VarbinaryToVarcharHex (@.VarbinaryValue
varbinary(4000))
RETURNS Varchar(8000) AS
BEGIN
Declare @.NumberOfBytes Int
Declare @.LeftByte Int
Declare @.RightByte Int
SET @.NumberOfBytes = datalength(@.VarbinaryValue)
IF (@.NumberOfBytes > 4)
RETURN Payment.dbo.ufn_VarbinaryToVarcharHex(cast(substri ng(@.VarbinaryValue,
1,
(@.NumberOfBytes/2)) as varbinary(2000)))
+ Payment.dbo.ufn_VarbinaryToVarcharHex(cast(substri ng(@.VarbinaryValue,
((@.NumberOfBytes/2)+1),
2000) as varbinary(2000)))
IF (@.NumberOfBytes = 0)
RETURN ''
-- Either 4 or less characters (8 hex digits) were input
SET @.LeftByte = CAST(@.VarbinaryValue as Int) & 15
SET @.LeftByte = CASE WHEN (@.LeftByte < 10)
THEN (48 + @.LeftByte)
ELSE (87 + @.LeftByte)
END
SET @.RightByte = (CAST(@.VarbinaryValue as Int) / 16) & 15
SET @.RightByte = CASE WHEN (@.RightByte < 10)
THEN (48 + @.RightByte)
ELSE (87 + @.RightByte)
END
SET @.VarbinaryValue = SUBSTRING(@.VarbinaryValue, 1,
(@.NumberOfBytes-1))
RETURN CASE WHEN (@.LeftByte < 10)
THEN
Payment.dbo.ufn_VarbinaryToVarcharHex(@.VarbinaryVa lue) +
char(@.RightByte) + char(@.LeftByte)
ELSE
Payment.dbo.ufn_VarbinaryToVarcharHex(@.VarbinaryVa lue) +
char(@.RightByte) + char(@.LeftByte)
END
END
go
GRANT EXECUTE ON [dbo].[ufn_VarbinaryToVarcharHex] TO [PUBLIC]
GO|||Ok, I've had a few emails on this, so I'll post the code here.
This is the code for the second component, a user stored procedure to
script out your diagrams, in the form of a new SQL script which will
populate dtproperties appropriately.
The formatting of the code pasted here got a little messed up with the
line wraps, but you should be able to clean that up easily enough in
SQL Query Analyzer.
-Clay
if exists (select 1
from sysobjects
where name = 'usp_ScriptDatabaseDiagrams'
and type = 'P')
drop procedure usp_ScriptDatabaseDiagrams
GO
CREATE PROCEDURE dbo.usp_ScriptDatabaseDiagrams @.DiagramName varchar
(128) = null
AS
-- Variable Declarations
--------
Declare @.idint
Declare @.objectidint
Declare @.propertyvarchar(64)
Declare @.valuevarchar (255)
Declare @.uvaluevarchar (255)
Declare @.lvaluePresentbit
Declare @.versionint
Declare @.PointerToDatavarbinary (16)
Declare @.ImageRowByteCountint
Declare @.CharDatavarchar (8000)
Declare @.DiagramDataFetchStatusint
Declare @.CharDataFetchStatusint
Declare @.Offsetint
Declare @.LastObjectidint
Declare @.NextObjectidint
Declare @.ReturnCodeint
-- Initializations
------
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
SET @.ReturnCode = -1
SET @.ImageRowByteCount = 40
SET @.LastObjectid = -1
SET @.NextObjectid = -1
-- Temp Table Creation for transforming Image Data into a text (hex)
format
-----------------------
CREATE TABLE #ImageData(KeyValue int NOT NULL IDENTITY (1, 1),
DataFieldvarbinary(8000) NULL) ON [PRIMARY]
-- Check for an unexpected error
----------
IF (@.@.error != 0)
BEGIN
PRINT ''
PRINT '***'
PRINT '*** ERROR OCCURRED WHILE ATTEMPTING TO CREATE TABLE
#ImageData'
PRINT '***'
PRINT ''
GOTO Procedure_Exit
END
ALTER TABLE #ImageData ADD CONSTRAINT
PK_ImageData PRIMARY KEY CLUSTERED
(KeyValue) ON [PRIMARY]
-- Check for an unexpected error
----------
IF (@.@.error != 0)
BEGIN
PRINT ''
PRINT '***'
PRINT '*** ERROR OCCURRED WHILE ATTEMPTING TO Index TABLE
#ImageData'
PRINT '***'
PRINT ''
GOTO Procedure_Exit
END
-- Output Script Header Documentation
------------
PRINT '----------------------'
PRINT '-- Database Diagram Reconstruction Script'
PRINT '----------------------'
PRINT '-- Created on: ' + Convert(varchar(23), GetDate(), 121)
PRINT '-- From Database: ' + DB_NAME()
PRINT '-- By User: ' + USER_NAME()
PRINT '--'
PRINT '-- This SQL Script was designed to reconstruct a set of
database'
PRINT '-- diagrams, by repopulating the system table dtproperties, in
the'
PRINT '-- current database, with values which existed at the time
this'
PRINT '-- script was created. Typically, this script would be created
to'
PRINT '-- backup a set of database diagrams, or to package up those
diagrams'
PRINT '-- for deployment to another database.'
PRINT '--'
PRINT '-- Minimally, all that needs to be done to recreate the target'
PRINT '-- diagrams is to run this script. There are several options,'
PRINT '-- however, which may be modified, to customize the diagrams to
be'
PRINT '-- produced. Changing these options is as simple as modifying
the'
PRINT '-- initial values for a set of variables, which are defined
immediately'
PRINT '-- following these comments. They are:'
PRINT '--'
PRINT '-- Variable Name Description'
PRINT '-- --------
--------------'
PRINT '-- @.TargetDatabase This varchar variable will establish
the'
PRINT '-- target database, within which the
diagrams'
PRINT '-- will be reconstructed. This variable
is'
PRINT '-- initially set to database name from
which the'
PRINT '-- script was built, but it may be
modified as'
PRINT '-- required. A valid database name
must be'
PRINT '-- specified.'
PRINT '--'
PRINT '-- @.DropExistingDiagrams This bit variable is initially set
set to a'
PRINT '-- value of zero (0), which indicates
that any'
PRINT '-- existing diagrams in the target
database are'
PRINT '-- to be preserved. By setting this
value to'
PRINT '-- one (1), any existing diagrams in
the target'
PRINT '-- database will be dropped prior to'
PRINT '-- reconstruction. Zero and One are the
only'
PRINT '-- valid values for the variable.'
PRINT '--'
PRINT '-- @.DiagramSuffix This varchar variable will be used
to append'
PRINT '-- to the original diagram names, as
they'
PRINT '-- existed at the time they were
scripted. This'
PRINT '-- variable is initially set to take on
the'
PRINT '-- value of the current date/time,
although it'
PRINT '-- may be modified as required. An
empty string'
PRINT '-- value would effectively turn off the
diagram'
PRINT '-- suffix option.'
PRINT '--'
PRINT '----------------------'
PRINT ''
PRINT 'SET NOCOUNT ON'
PRINT ''
PRINT '-- User Settable Options'
PRINT '--------'
PRINT 'Declare @.TargetDatabase varchar (128)'
PRINT 'Declare @.DropExistingDiagrams bit'
PRINT 'Declare @.DiagramSuffix varchar (50)'
PRINT ''
PRINT '-- Initialize User Settable Options'
PRINT '-----------'
PRINT 'SET @.TargetDatabase = ''Payment'''
PRINT 'SET @.DropExistingDiagrams = 0'
PRINT 'SET @.DiagramSuffix = '' '' + Convert(varchar(23), GetDate(),
121)'
PRINT ''
PRINT ''
PRINT '----------------------'
PRINT '-- END OF USER MODIFIABLE SECTION - MAKE NO CHANGES TO THE
LOGIC BELOW --'
PRINT '----------------------'
PRINT ''
PRINT ''
PRINT '-- Setting Target database and clearing dtproperties, if
indicated'
PRINT '--------------------'
PRINT 'Exec(''USE '' + @.TargetDatabase)'
PRINT 'IF (@.DropExistingDiagrams = 1)'
PRINT ' TRUNCATE TABLE dtproperties'
PRINT ''
PRINT ''
PRINT '-- Creating Temp Table to persist specific variables '
PRINT '-- between Transact SQL batches (between GO statements)'
PRINT '-----------------'
PRINT 'IF EXISTS(SELECT 1'
PRINT ' FROM tempdb..sysobjects'
PRINT ' WHERE name like ''%#PersistedVariables%'''
PRINT ' AND xtype = ''U'')'
PRINT ' DROP TABLE #PersistedVariables'
PRINT 'CREATE TABLE #PersistedVariables (VariableName varchar (50)
NOT NULL,'
PRINT ' VariableValue varchar (50)
NOT NULL) ON [PRIMARY]'
PRINT 'ALTER TABLE #PersistedVariables ADD CONSTRAINT'
PRINT ' PK_PersistedVariables PRIMARY KEY CLUSTERED '
PRINT ' (VariableName) ON [PRIMARY]'
PRINT ''
PRINT ''
PRINT '-- Persist @.DiagramSuffix'
PRINT '--------'
PRINT 'INSERT INTO #PersistedVariables VALUES (''DiagramSuffix'','
PRINT ' @.DiagramSuffix)'
PRINT 'GO'
PRINT ''
-- Cusror to be used to enumerate through each row of
-- diagram data from the table dtproperties
----------------
Declare DiagramDataCursor Cursor
FOR SELECT dtproperties.id,
dtproperties.objectid,
dtproperties.property,
dtproperties.value,
dtproperties.uvalue,
CASE WHEN (dtproperties.lvalue is Null) THEN 0
ELSE 1
END,
dtproperties.version
FROM dtproperties INNER JOIN (SELECT objectid
FROM dtproperties
WHERE property = 'DtgSchemaNAME'
AND value =
IsNull(@.DiagramName, value)) TargetObject
ON dtproperties.objectid =
TargetObject.objectid
ORDER BY dtproperties.id,
dtproperties.objectid
-- Check for an unexpected error
----------
IF (@.@.error != 0)
BEGIN
PRINT ''
PRINT '***'
PRINT '*** ERROR OCCURRED WHILE ATTEMPTING TO DECLARE CURSOR
DiagramDataCursor'
PRINT '***'
PRINT ''
GOTO Procedure_Exit
END
-- Cusror to be used to enumerate through each row of
-- varchar data from the temp table #ImageData
----------------
Declare CharDataCursor Cursor
FOR SELECT '0x'+Payment.dbo.ufn_VarbinaryToVarcharHex(DataFie ld)
FROM #ImageData
ORDER BY KeyValue
-- Check for an unexpected error
----------
IF (@.@.error != 0)
BEGIN
PRINT ''
PRINT '***'
PRINT '*** ERROR OCCURRED WHILE ATTEMPTING TO DECLARE CURSOR
CharDataCursor'
PRINT '***'
PRINT ''
GOTO Procedure_Exit
END
-- Open the DiagramDataCursor cursor
-----------
OPEN DiagramDataCursor
-- Check for an unexpected error
----------
IF (@.@.error != 0)
BEGIN
PRINT ''
PRINT '***'
PRINT '*** ERROR OCCURRED WHILE ATTEMPTING TO OPEN CURSOR
DiagramDataCursor'
PRINT '***'
PRINT ''
GOTO Procedure_Exit
END
-- Get the Row of Diagram data
----------
FETCH NEXT FROM DiagramDataCursor
INTO @.id,
@.objectid,
@.property,
@.value,
@.uvalue,
@.lvaluePresent,
@.version
-- Check for an unexpected error
----------
IF (@.@.error != 0)
BEGIN
PRINT ''
PRINT '***'
PRINT '*** ERROR OCCURRED WHILE ATTEMPTING TO FETCH NEXT FROM
CURSOR DiagramDataCursor'
PRINT '***'
PRINT ''
GOTO Procedure_Exit
END
-- Initialize the Fetch Status for the DiagramDataCursor cursor
-------------------
SET @.DiagramDataFetchStatus = @.@.FETCH_STATUS
-- Check for an unexpected error
----------
IF (@.@.error != 0)
BEGIN
PRINT ''
PRINT '***'
PRINT '*** ERROR OCCURRED WHILE ATTEMPTING TO SET
@.DiagramDataFetchStatus'
PRINT '***'
PRINT ''
GOTO Procedure_Exit
END
-- Begin the processing each Row of Diagram data
---------------
WHILE (@.DiagramDataFetchStatus = 0)
BEGIN
-- Build an Insert statement for non-image data
PRINT ''
PRINT '-- Insert a new dtproperties row'
PRINT '----------'
IF (@.LastObjectid <> @.objectid)
BEGIN
-- Retrieve the persisted DiagramSuffix - If
processing DtgSchemaNAME
IF (@.property = 'DtgSchemaNAME')
BEGIN
PRINT 'Declare @.DiagramSuffix varchar (50)'
PRINT 'SELECT @.DiagramSuffix = Convert(varchar
(50), VariableValue)'
PRINT 'FROM #PersistedVariables'
PRINT 'WHERE VariableName = ''DiagramSuffix'''
END
-- Build the Insert statement for a New Diagram -
Apply and Persist the new Objectid
PRINT 'INSERT INTO dtproperties (objectid,'
PRINT ' property,'
PRINT ' value,'
PRINT ' uvalue,'
PRINT ' lvalue,'
PRINT ' version)'
PRINT ' VALUES (0,'
PRINT ' ''' + @.property +
''','
PRINT ' ' + CASE WHEN
(@.property = 'DtgSchemaNAME')
THEN
IsNull(('''' + @.value + ''' + @.DiagramSuffix,'), 'null,')
ELSE
IsNull(('''' + @.value + ''','), 'null,')
END
PRINT ' ' + CASE WHEN
(@.property = 'DtgSchemaNAME')
THEN
IsNull(('''' + @.uvalue + '''+ @.DiagramSuffix,'), 'null,')
ELSE
IsNull(('''' + @.uvalue + ''','), 'null,')
END
PRINT ' ' + CASE WHEN
(@.lvaluePresent = 1)
THEN
'cast(''0'' as varbinary(10)),'
ELSE
'null,'
END
PRINT ' ' +
IsNull(Convert(varchar(15), @.version), 'null') + ')'
PRINT 'DELETE #PersistedVariables'
PRINT 'WHERE VariableName = ''NextObjectid'''
PRINT 'INSERT INTO #PersistedVariables VALUES
(''NextObjectid'','
PRINT '
Convert(varchar(15), @.@.IDENTITY))'
PRINT 'Declare @.NextObjectid int'
PRINT 'SELECT @.NextObjectid = Convert(int,
VariableValue)'
PRINT 'FROM #PersistedVariables'
PRINT 'WHERE VariableName = ''NextObjectid'''
PRINT 'UPDATE dtproperties'
PRINT ' SET Objectid = @.NextObjectid'
PRINT 'WHERE id = @.NextObjectid'
SET @.LastObjectid = @.objectid
END
ELSE
BEGIN
-- Retrieve the persisted DiagramSuffix - If
processing DtgSchemaNAME
IF (@.property = 'DtgSchemaNAME')
BEGIN
PRINT 'Declare @.DiagramSuffix varchar (50)'
PRINT 'SELECT @.DiagramSuffix = Convert(varchar
(50), VariableValue)'
PRINT 'FROM #PersistedVariables'
PRINT 'WHERE VariableName = ''DiagramSuffix'''
END
-- Build the Insert statement for an in process
Diagram - Retrieve the persisted Objectid
PRINT 'Declare @.NextObjectid int'
PRINT 'SELECT @.NextObjectid = Convert(int,
VariableValue)'
PRINT 'FROM #PersistedVariables'
PRINT 'WHERE VariableName = ''NextObjectid'''
PRINT 'INSERT INTO dtproperties (objectid,'
PRINT ' property,'
PRINT ' value,'
PRINT ' uvalue,'
PRINT ' lvalue,'
PRINT ' version)'
PRINT ' VALUES (@.NextObjectid,'
PRINT ' ''' + @.property +
''','
PRINT ' ' + CASE WHEN
(@.property = 'DtgSchemaNAME')
THEN
IsNull(('''' + @.value + ''' + @.DiagramSuffix,'), 'null,')
ELSE
IsNull(('''' + @.value + ''','), 'null,')
END
PRINT ' ' + CASE WHEN
(@.property = 'DtgSchemaNAME')
THEN
IsNull(('''' + @.uvalue + '''+ @.DiagramSuffix,'), 'null,')
ELSE
IsNull(('''' + @.uvalue + ''','), 'null,')
END
PRINT ' ' + CASE WHEN
(@.lvaluePresent = 1)
THEN
'cast(''0'' as varbinary(10)),'
ELSE
'null,'
END
PRINT ' ' +
IsNull(Convert(varchar(15), @.version), 'null') + ')'
END
-- Each Insert deliniates a new Transact SQL batch
PRINT 'GO'
-- Check for a non-null lvalue (image data is present)
IF (@.lvaluePresent = 1)
BEGIN
-- Fill the temp table with Image Data of length @.ImageRowByteCount
INSERT INTO #ImageData (DataField)
EXEC usp_dtpropertiesTextToRowset @.id,
@.ImageRowByteCount
-- Check for an unexpected error
IF (@.@.error != 0)
BEGIN
PRINT ''
PRINT '***'
PRINT '*** ERROR OCCURRED WHILE ATTEMPTING TO
INSERT INTO #ImageData'
PRINT '***'
PRINT ''
GOTO Procedure_Exit
END
-- Prepare to build the UPDATETEXT statement(s) for
the image data
SET @.Offset = 0
-- Open the CharDataCursor cursor
OPEN CharDataCursor
-- Check for an unexpected error
IF (@.@.error != 0)
BEGIN
PRINT ''
PRINT '***'
PRINT '*** ERROR OCCURRED WHILE ATTEMPTING TO
OPEN CURSOR CharDataCursor'
PRINT '***'
PRINT ''
GOTO Procedure_Exit
END
-- Get the CharData Row
FETCH NEXT FROM CharDataCursor
INTO @.CharData
-- Check for an unexpected error
IF (@.@.error != 0)
BEGIN
PRINT ''
PRINT '***'
PRINT '*** ERROR OCCURRED WHILE ATTEMPTING TO
FETCH NEXT FROM CURSOR CharDataCursor'
PRINT '***'
PRINT ''
GOTO Procedure_Exit
END
-- Initialize the Fetch Status for the CharDataCursor
cursor
SET @.CharDataFetchStatus = @.@.FETCH_STATUS
-- Check for an unexpected error
IF (@.@.error != 0)
BEGIN
PRINT ''
PRINT '***'
PRINT '*** ERROR OCCURRED WHILE ATTEMPTING TO
SET @.CharDataFetchStatus'
PRINT '***'
PRINT ''
GOTO Procedure_Exit
END
-- Begin the processing of each Row of Char data
WHILE (@.CharDataFetchStatus = 0)
BEGIN
-- Update a segment of image data
PRINT ''
PRINT '-- Update this dtproperties row with a
new segment of Image data'
PRINT 'Declare @.PointerToData varbinary (16)'
PRINT 'SELECT @.PointerToData = TEXTPTR(lvalue)
FROM dtproperties WHERE id = (SELECT MAX(id) FROM dtproperties)'
PRINT 'UPDATETEXT dtproperties.lvalue
@.PointerToData ' + convert(varchar(15), @.Offset) + ' null ' +
@.CharData
-- Each UPDATETEXT deliniates a new Transact
SQL batch
PRINT 'GO'
-- Calculate the Offset for the next segment
of image data
SET @.Offset = @.Offset + ((LEN(@.CharData) - 2)
/ 2)
-- Get the CharData Row
FETCH NEXT FROM CharDataCursor
INTO @.CharData
-- Check for an unexpected error
IF (@.@.error != 0)
BEGIN
PRINT ''
PRINT '***'
PRINT '*** ERROR OCCURRED WHILE
ATTEMPTING TO FETCH NEXT FROM CURSOR CharDataCursor'
PRINT '***'
PRINT ''
GOTO Procedure_Exit
END
-- Update the Fetch Status for the
CharDataCursor cursor
SET @.CharDataFetchStatus = @.@.FETCH_STATUS
-- Check for an unexpected error
IF (@.@.error != 0)
BEGIN
PRINT ''
PRINT '***'
PRINT '*** ERROR OCCURRED WHILE
ATTEMPTING TO SET @.CharDataFetchStatus'
PRINT '***'
PRINT ''
GOTO Procedure_Exit
END
END
-- Cleanup CharDataCursor Cursor resources
Close CharDataCursor
-- Check for an unexpected error
IF (@.@.error != 0)
BEGIN
PRINT ''
PRINT '***'
PRINT '*** ERROR OCCURRED WHILE ATTEMPTING TO
CLOSE CURSOR CharDataCursor'
PRINT '***'
PRINT ''
GOTO Procedure_Exit
END
-- Flush the processed Image data
TRUNCATE TABLE #ImageData
-- Check for an unexpected error
IF (@.@.error != 0)
BEGIN
PRINT ''
PRINT '***'
PRINT '*** ERROR OCCURRED WHILE ATTEMPTING TO
TRUNCATE TABLE #ImageData'
PRINT '***'
PRINT ''
GOTO Procedure_Exit
END
END
-- Get the Row of Diagram data
FETCH NEXT FROM DiagramDataCursor
INTO @.id,
@.objectid,
@.property,
@.value,
@.uvalue,
@.lvaluePresent,
@.version
-- Check for an unexpected error
IF (@.@.error != 0)
BEGIN
PRINT ''
PRINT '***'
PRINT '*** ERROR OCCURRED WHILE ATTEMPTING TO FETCH
NEXT FROM CURSOR DiagramDataCursor'
PRINT '***'
PRINT ''
GOTO Procedure_Exit
END
-- Update the Fetch Status for the DiagramDataCursor cursor
SET @.DiagramDataFetchStatus = @.@.FETCH_STATUS
-- Check for an unexpected error
IF (@.@.error != 0)
BEGIN
PRINT ''
PRINT '***'
PRINT '*** ERROR OCCURRED WHILE ATTEMPTING TO SET
@.DiagramDataFetchStatus'
PRINT '***'
PRINT ''
GOTO Procedure_Exit
END
END
PRINT ''
PRINT '-- Cleanup the temp table #PersistedVariables'
PRINT '--------------'
PRINT 'IF EXISTS(SELECT 1'
PRINT ' FROM tempdb..sysobjects'
PRINT ' WHERE name like ''%#PersistedVariables%'''
PRINT ' AND xtype = ''U'')'
PRINT ' DROP TABLE #PersistedVariables'
PRINT 'GO'
PRINT ''
PRINT 'SET NOCOUNT OFF'
PRINT 'GO'
-- Processing Complete
-------
SET @.ReturnCode = 0
Procedure_Exit:
-----
Close DiagramDataCursor
DEALLOCATE DiagramDataCursor
DEALLOCATE CharDataCursor
DROP TABLE #ImageData
SET NOCOUNT OFF
RETURN @.ReturnCode
GO
GRANT EXECUTE ON [dbo].[usp_ScriptDatabaseDiagrams] TO [Public]
GO|||Yikes!!! SOmeone just correctly pointed out to me that there are
actually three components which I should have posted... I neglected
to post the stored procedure: usp_dtpropertiesTextToRowset Which is
required for the process to work.
Here it is, the third component... This should be built after the
function, but before the other procedure, since the other procedure
references this one.
-Clay
if exists (select 1
from sysobjects
where name = 'usp_dtpropertiesTextToRowset'
and type = 'P')
drop procedure usp_dtpropertiesTextToRowset
GO
CREATE PROCEDURE dbo.usp_dtpropertiesTextToRowset @.idint,
@.RowsetCharLenint =
255
AS
-- Variable Declarations
--------
Declare @.PointerToDatavarbinary (16)
Declare @.TotalSizeint
Declare @.LastReadint
Declare @.ReadSizeint
Declare @.ReturnCodeint
-- Initializations
------
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
SET @.ReturnCode = -1
-- Establish the Pointer to the Image data
-------------
SELECT @.PointerToData = TEXTPTR(lvalue),
@.TotalSize = DATALENGTH(lvalue),
@.LastRead = 0,
@.ReadSize = CASE WHEN (@.RowsetCharLen < DATALENGTH(lvalue))
THEN @.RowsetCharLen
ELSE DATALENGTH(lvalue)
END
FROM dtproperties
WHERE id = @.id
-- Loop through the image data, returning rows of the desired length
---------------------
IF (@.PointerToData is not null) AND
(@.ReadSize > 0)
WHILE (@.LastRead < @.TotalSize)
BEGIN
IF ((@.ReadSize + @.LastRead) > @.TotalSize)
SET @.ReadSize = @.TotalSize - @.LastRead
READTEXT dtproperties.lvalue @.PointerToData @.LastRead
@.ReadSize
SET @.LastRead = @.LastRead + @.ReadSize
END
-- Processing Complete
-------
SET @.ReturnCode = 0
Procedure_Exit:
-----
SET NOCOUNT OFF
RETURN @.ReturnCode
GO
GRANT EXECUTE ON [dbo].[usp_dtpropertiesTextToRowset] TO [Public]
GO|||Yikes!!! SOmeone just correctly pointed out to me that there are
actually three components which I should have posted... I neglected
to post the stored procedure: usp_dtpropertiesTextToRowset Which is
required for the process to work.
Here it is, the third component... This should be built after the
function, but before the other procedure, since the other procedure
references this one.
-Clay
if exists (select 1
from sysobjects
where name = 'usp_dtpropertiesTextToRowset'
and type = 'P')
drop procedure usp_dtpropertiesTextToRowset
GO
CREATE PROCEDURE dbo.usp_dtpropertiesTextToRowset @.idint,
@.RowsetCharLenint =
255
AS
-- Variable Declarations
--------
Declare @.PointerToDatavarbinary (16)
Declare @.TotalSizeint
Declare @.LastReadint
Declare @.ReadSizeint
Declare @.ReturnCodeint
-- Initializations
------
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
SET @.ReturnCode = -1
-- Establish the Pointer to the Image data
-------------
SELECT @.PointerToData = TEXTPTR(lvalue),
@.TotalSize = DATALENGTH(lvalue),
@.LastRead = 0,
@.ReadSize = CASE WHEN (@.RowsetCharLen < DATALENGTH(lvalue))
THEN @.RowsetCharLen
ELSE DATALENGTH(lvalue)
END
FROM dtproperties
WHERE id = @.id
-- Loop through the image data, returning rows of the desired length
---------------------
IF (@.PointerToData is not null) AND
(@.ReadSize > 0)
WHILE (@.LastRead < @.TotalSize)
BEGIN
IF ((@.ReadSize + @.LastRead) > @.TotalSize)
SET @.ReadSize = @.TotalSize - @.LastRead
READTEXT dtproperties.lvalue @.PointerToData @.LastRead
@.ReadSize
SET @.LastRead = @.LastRead + @.ReadSize
END
-- Processing Complete
-------
SET @.ReturnCode = 0
Procedure_Exit:
-----
SET NOCOUNT OFF
RETURN @.ReturnCode
GO
GRANT EXECUTE ON [dbo].[usp_dtpropertiesTextToRowset] TO [Public]
GO|||One last note on the subject...
You might have already noticed, but I had coded two of the components
to include a reference to the database which I work with (Payment).
You'll need to change that name, in the function
ufn_VarbinaryToVarcharHex, and in the procedure
usp_ScriptDatabaseDiagrams, to reflect the database name which you are
working with.
-Clay|||One last note on the subject...
You might have already noticed, but I had coded two of the components
to include a reference to the database which I work with (Payment).
You'll need to change that name, in the function
ufn_VarbinaryToVarcharHex, and in the procedure
usp_ScriptDatabaseDiagrams, to reflect the database name which you are
working with.
-Clay
Script Transform
Hi,
My requirement is to check whether value of a particular column is null or not. if it is null I have to enter warning messages into the temp table I have created.
For this I am using Script Transform
Now I want to know how to write info from script transform to a table using SSIS.
Currently I am using the following code in script component
[Code]
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim ConnString As String
ConnString = "Data Source=ABC;Initial Catalog=XXXX;Integrated Security=SSPI;"
Dim sqcn As New SqlConnection(ConnString)
''Dim sqlCmd As New SqlCommand(ConnString, sqcn)
'sqcn.Open()
if Row.Col1_IsNull Then
sqlCmd.CommandText = "Insert into AuditLog values('ERROR','Missing','" + Row.Col5 + "','" + Row.Col6 + "') "
sqlCmd.ExecuteNonQuery()
end if
End Sub
[/Code]
Without using SqlConnection and SqlCommand is thereany way I can get the Connection Obj and able to insert rec in the table.
Thanks
You can create ADO.NET Connection Manager object in the package, get it in script transfrom, and the AquireConnection method returns the managed connection object.
But why do you need script transform at all? You can use conditional split transform to find rows with missing column, and direct the output of conditional split transform to Sql Server or OLEDB Destination.
Monday, March 26, 2012
Script to generate INSERT statements on table
I'd I have a problem I'd like to post CREATE TABLE and INSERT statements
that will create my table and insert data into the table.
that will create my table and insert data into the table.
I can use the scripting feature in Enterprise Manager to generate CREATE
TABLE scripts.
Is there a script I can run that will generate INSERT statements so I can
include sample data.
ThanksYou can download a free T-SQL script to do this:
http://vyaskn.tripod.com/code.htm#inserts
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Terri" <Terri@.spamaway.com> wrote in message
news:c10ufs$v54$1@.reader2.nmix.net...
> I'd I have a problem I'd like to post CREATE TABLE and INSERT statements
> that will create my table and insert data into the table.
> I can use the scripting feature in Enterprise Manager to generate CREATE
> TABLE scripts.
> Is there a script I can run that will generate INSERT statements so I can
> include sample data.
> Thanks
Script to find out the number of row in all the table of a database.
HI,
I am using following script to find out the number of row in all the
table of a database is there any simple way out? if so pls mail me
declare @.TAB VARCHAR (20),
@.qu nvarchar (100)
DECLARE TABALE CURSOR FOR
select name from sysobjects where xtype='u' order by name open tabale
FETCH NEXT FROM TABALE INTO @.TAB while @.@.fetch_status = 0 begin --SET
@.TAB = 'SALES1'
--select name from sysobjects where name = @.tab SET @.QU ='SELECT
COUNT(*) FROM '+@.TAB print @.tab EXEC sp_executesql @.QU fetch next from
TABALE INTO @.TAB end close tabale deallocate tabale
Thanks
Sajid ChhapekarYOu could use the undocumented procedure sp_msforeachtable, but keep in
mind that this one is undocumented and might be deprecated in further
versions of SQL Server.
sp_msforeachtable 'SELECT ''?'' as TableName COUNT(*) AS Counted_rows
FROM ?'
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--|||Hello,
Try the following query to get your row counts without having to use a cursor.
SELECT sysobjects.name, rows FROM Sysindexes
INNER JOIN Sysobjects
on Sysindexes.id = sysobjects.id
AND indid < 2
AND sysobjects.xtype = 'u'
AND sysobjects.name <> 'dtproperties'
You should get the same result as your cursor.
Thanks Kllyj64
"csajid@.gmail.com" wrote:
> HI,
> I am using following script to find out the number of row in all the
> table of a database is there any simple way out? if so pls mail me
>
> declare @.TAB VARCHAR (20),
> @.qu nvarchar (100)
> DECLARE TABALE CURSOR FOR
> select name from sysobjects where xtype='u' order by name open tabale
> FETCH NEXT FROM TABALE INTO @.TAB while @.@.fetch_status = 0 begin --SET
> @.TAB = 'SALES1'
> --select name from sysobjects where name = @.tab SET @.QU ='SELECT
> COUNT(*) FROM '+@.TAB print @.tab EXEC sp_executesql @.QU fetch next from
> TABALE INTO @.TAB end close tabale deallocate tabale
>
> Thanks
> Sajid Chhapekar
>|||Hi,
That's great. Thanks for this.
Thanks and regards,
Sajid.
kllyj64 wrote:
> Hello,
> Try the following query to get your row counts without having to use a cursor.
> SELECT sysobjects.name, rows FROM Sysindexes
> INNER JOIN Sysobjects
> on Sysindexes.id = sysobjects.id
> AND indid < 2
> AND sysobjects.xtype = 'u'
> AND sysobjects.name <> 'dtproperties'
> You should get the same result as your cursor.
>
> --
> Thanks Kllyj64
>
> "csajid@.gmail.com" wrote:
> > HI,
> >
> > I am using following script to find out the number of row in all the
> > table of a database is there any simple way out? if so pls mail me
> >
> >
> > declare @.TAB VARCHAR (20),
> > @.qu nvarchar (100)
> >
> > DECLARE TABALE CURSOR FOR
> > select name from sysobjects where xtype='u' order by name open tabale
> > FETCH NEXT FROM TABALE INTO @.TAB while @.@.fetch_status = 0 begin --SET
> > @.TAB = 'SALES1'
> > --select name from sysobjects where name = @.tab SET @.QU ='SELECT
> > COUNT(*) FROM '+@.TAB print @.tab EXEC sp_executesql @.QU fetch next from
> > TABALE INTO @.TAB end close tabale deallocate tabale
> >
> >
> > Thanks
> > Sajid Chhapekar
> >
> >
I am using following script to find out the number of row in all the
table of a database is there any simple way out? if so pls mail me
declare @.TAB VARCHAR (20),
@.qu nvarchar (100)
DECLARE TABALE CURSOR FOR
select name from sysobjects where xtype='u' order by name open tabale
FETCH NEXT FROM TABALE INTO @.TAB while @.@.fetch_status = 0 begin --SET
@.TAB = 'SALES1'
--select name from sysobjects where name = @.tab SET @.QU ='SELECT
COUNT(*) FROM '+@.TAB print @.tab EXEC sp_executesql @.QU fetch next from
TABALE INTO @.TAB end close tabale deallocate tabale
Thanks
Sajid ChhapekarYOu could use the undocumented procedure sp_msforeachtable, but keep in
mind that this one is undocumented and might be deprecated in further
versions of SQL Server.
sp_msforeachtable 'SELECT ''?'' as TableName COUNT(*) AS Counted_rows
FROM ?'
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--|||Hello,
Try the following query to get your row counts without having to use a cursor.
SELECT sysobjects.name, rows FROM Sysindexes
INNER JOIN Sysobjects
on Sysindexes.id = sysobjects.id
AND indid < 2
AND sysobjects.xtype = 'u'
AND sysobjects.name <> 'dtproperties'
You should get the same result as your cursor.
Thanks Kllyj64
"csajid@.gmail.com" wrote:
> HI,
> I am using following script to find out the number of row in all the
> table of a database is there any simple way out? if so pls mail me
>
> declare @.TAB VARCHAR (20),
> @.qu nvarchar (100)
> DECLARE TABALE CURSOR FOR
> select name from sysobjects where xtype='u' order by name open tabale
> FETCH NEXT FROM TABALE INTO @.TAB while @.@.fetch_status = 0 begin --SET
> @.TAB = 'SALES1'
> --select name from sysobjects where name = @.tab SET @.QU ='SELECT
> COUNT(*) FROM '+@.TAB print @.tab EXEC sp_executesql @.QU fetch next from
> TABALE INTO @.TAB end close tabale deallocate tabale
>
> Thanks
> Sajid Chhapekar
>|||Hi,
That's great. Thanks for this.
Thanks and regards,
Sajid.
kllyj64 wrote:
> Hello,
> Try the following query to get your row counts without having to use a cursor.
> SELECT sysobjects.name, rows FROM Sysindexes
> INNER JOIN Sysobjects
> on Sysindexes.id = sysobjects.id
> AND indid < 2
> AND sysobjects.xtype = 'u'
> AND sysobjects.name <> 'dtproperties'
> You should get the same result as your cursor.
>
> --
> Thanks Kllyj64
>
> "csajid@.gmail.com" wrote:
> > HI,
> >
> > I am using following script to find out the number of row in all the
> > table of a database is there any simple way out? if so pls mail me
> >
> >
> > declare @.TAB VARCHAR (20),
> > @.qu nvarchar (100)
> >
> > DECLARE TABALE CURSOR FOR
> > select name from sysobjects where xtype='u' order by name open tabale
> > FETCH NEXT FROM TABALE INTO @.TAB while @.@.fetch_status = 0 begin --SET
> > @.TAB = 'SALES1'
> > --select name from sysobjects where name = @.tab SET @.QU ='SELECT
> > COUNT(*) FROM '+@.TAB print @.tab EXEC sp_executesql @.QU fetch next from
> > TABALE INTO @.TAB end close tabale deallocate tabale
> >
> >
> > Thanks
> > Sajid Chhapekar
> >
> >
Script to find out the number of row in all the table of a database.
HI,
I am using following script to find out the number of row in all the
table of a database is there any simple way out? if so pls mail me
declare @.TAB VARCHAR (20),
@.qu nvarchar (100)
DECLARE TABALE CURSOR FOR
select name from sysobjects where xtype='u' order by name open tabale
FETCH NEXT FROM TABALE INTO @.TAB while @.@.fetch_status = 0 begin --SET
@.TAB = 'SALES1'
--select name from sysobjects where name = @.tab SET @.QU ='SELECT
COUNT(*) FROM '+@.TAB print @.tab EXEC sp_executesql @.QU fetch next from
TABALE INTO @.TAB end close tabale deallocate tabale
Thanks
Sajid ChhapekarYOu could use the undocumented procedure sp_msforeachtable, but keep in
mind that this one is undocumented and might be deprecated in further
versions of SQL Server.
sp_msforeachtable 'SELECT ''?'' as TableName COUNT(*) AS Counted_rows
FROM ?'
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--sql
I am using following script to find out the number of row in all the
table of a database is there any simple way out? if so pls mail me
declare @.TAB VARCHAR (20),
@.qu nvarchar (100)
DECLARE TABALE CURSOR FOR
select name from sysobjects where xtype='u' order by name open tabale
FETCH NEXT FROM TABALE INTO @.TAB while @.@.fetch_status = 0 begin --SET
@.TAB = 'SALES1'
--select name from sysobjects where name = @.tab SET @.QU ='SELECT
COUNT(*) FROM '+@.TAB print @.tab EXEC sp_executesql @.QU fetch next from
TABALE INTO @.TAB end close tabale deallocate tabale
Thanks
Sajid ChhapekarYOu could use the undocumented procedure sp_msforeachtable, but keep in
mind that this one is undocumented and might be deprecated in further
versions of SQL Server.
sp_msforeachtable 'SELECT ''?'' as TableName COUNT(*) AS Counted_rows
FROM ?'
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--sql
Friday, March 23, 2012
Script to copy entire table
I'm a newbie to script writing. I'm trying to write a script to copy all
data from a table to the same table in a 2nd database. Both databases are
on the same server and are identical in design. I can do this with DTS but
wanted a script I could email to a user to run in Query Analyzer.
data from a table to the same table in a 2nd database. Both databases are
on the same server and are identical in design. I can do this with DTS but
wanted a script I could email to a user to run in Query Analyzer.
Example:
Copy entire table called 'Customers' in the 'Data01' database to
table 'Customers' in the 'Data02' database
I want to overwrite all data in the destination table.
Thanks"sqlnewbie" <sqlnewbie@.yahoo.com> wrote in message
news:k4PYb.27374$vs5.13502@.newssvr25.news.prodigy. com...
> I'm a newbie to script writing. I'm trying to write a script to copy all
> data from a table to the same table in a 2nd database. Both databases are
> on the same server and are identical in design. I can do this with DTS
but
> wanted a script I could email to a user to run in Query Analyzer.
> Example:
> Copy entire table called 'Customers' in the 'Data01' database to
> table 'Customers' in the 'Data02' database
> I want to overwrite all data in the destination table.
> Thanks
/* Replace all data in the destination table */
use Data02
go
truncate table dbo.Customers
insert into dbo.Customers (col1, col2, ...)
select col1, col2, ...
from Data01.dbo.Customers
/* Insert only data which isn't already there */
use Data02
go
insert into dbo.Customers (col1, col2, ...)
select col1, col2, ...
from Data01.dbo.Customers c1
where not exists (select *
from dbo.Customers c2
where c1.PrimaryKeyCol = c2.PrimaryKeyCol)
Note that TRUNCATE TABLE requires certain permissions (see Books Online),
and won't work if the table is referenced by foreign keys. In this case, you
can use "DELETE FROM dbo.Customers".
I would be careful about sending scripts to users, as they often seem to run
them in the wrong place at the wrong time - moving data should really be a
DBA's task (although I appreciate that not everyone has a DBA available).
You may want to back up the database first, just in case.
Simonsql
Wednesday, March 21, 2012
Script that Return the number of Rows for each Table on a DB
I Have 2 separate data bases with the same Tables and records, I create
a batch to synchronize my data every 2 hours but now I need a Script
that Return the number of Rows for each Table on each DB.
Can someone give me an Idea or the solution for this, I will really
appreciate it.This is what the code I have, now I need to insert the total rows per
table
IF OBJECT_ID('tempdb..#TableSummary') IS NOT NULL
DROP TABLE #TableSummary
SELECT DISTINCT TABLE_NAME AS TableName, 0 as CountOfTable
INTO #TableSummary
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME IN
(SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE (TABLE_TYPE = 'BASE TABLE' AND
TABLE_NAME NOT IN ('dtproperties','TableSummary','AllUserT
ables')))
SELECT * FROM #TableSummary|||This is what I have, now I need to insert the total rows per
table
IF OBJECT_ID('tempdb..#TableSummary') IS NOT NULL
DROP TABLE #TableSummary
SELECT DISTINCT TABLE_NAME AS TableName, 0 as CountOfTable
INTO #TableSummary
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME IN
(SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE (TABLE_TYPE = 'BASE TABLE' AND
TABLE_NAME NOT IN
('dtproperties','TableSummary','AllUserT
ables')))
SELECT * FROM #TableSummary|||Run this query against each database:
SELECT DISTINCT TOP 100 PERCENT dbo.sysobjects.name, dbo.sysindexes.rowcnt
FROM dbo.sysobjects INNER JOIN
dbo.sysindexes ON dbo.sysobjects.id = dbo.sysindexes.id
WHERE (dbo.sysobjects.xtype = 'U') AND (dbo.sysindexes.status = 0)
ORDER BY dbo.sysobjects.name
"imagabo" wrote:
> I Have 2 separate data bases with the same Tables and records, I create
> a batch to synchronize my data every 2 hours but now I need a Script
> that Return the number of Rows for each Table on each DB.
> Can someone give me an Idea or the solution for this, I will really
> appreciate it.
>|||This is it, Thank you so much bschaettle, I own you one.
a batch to synchronize my data every 2 hours but now I need a Script
that Return the number of Rows for each Table on each DB.
Can someone give me an Idea or the solution for this, I will really
appreciate it.This is what the code I have, now I need to insert the total rows per
table
IF OBJECT_ID('tempdb..#TableSummary') IS NOT NULL
DROP TABLE #TableSummary
SELECT DISTINCT TABLE_NAME AS TableName, 0 as CountOfTable
INTO #TableSummary
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME IN
(SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE (TABLE_TYPE = 'BASE TABLE' AND
TABLE_NAME NOT IN ('dtproperties','TableSummary','AllUserT
ables')))
SELECT * FROM #TableSummary|||This is what I have, now I need to insert the total rows per
table
IF OBJECT_ID('tempdb..#TableSummary') IS NOT NULL
DROP TABLE #TableSummary
SELECT DISTINCT TABLE_NAME AS TableName, 0 as CountOfTable
INTO #TableSummary
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME IN
(SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE (TABLE_TYPE = 'BASE TABLE' AND
TABLE_NAME NOT IN
('dtproperties','TableSummary','AllUserT
ables')))
SELECT * FROM #TableSummary|||Run this query against each database:
SELECT DISTINCT TOP 100 PERCENT dbo.sysobjects.name, dbo.sysindexes.rowcnt
FROM dbo.sysobjects INNER JOIN
dbo.sysindexes ON dbo.sysobjects.id = dbo.sysindexes.id
WHERE (dbo.sysobjects.xtype = 'U') AND (dbo.sysindexes.status = 0)
ORDER BY dbo.sysobjects.name
"imagabo" wrote:
> I Have 2 separate data bases with the same Tables and records, I create
> a batch to synchronize my data every 2 hours but now I need a Script
> that Return the number of Rows for each Table on each DB.
> Can someone give me an Idea or the solution for this, I will really
> appreciate it.
>|||This is it, Thank you so much bschaettle, I own you one.
Script task: Bindingsource
I used a binding source in my script task codes to filter the data table. when i used that, even if i declare it (dim bs as bindingsource), i'm still having an error - "type bindingsource is not defined".
cherrie
Hi, bindingsource is part of the forms name space. Does your code includeImports System.Windows.Forms
Script task and OLEDB destination Performance
Hi fellows,
Sorry to disturb but just a question. I have a package which extracts all the records from table A and update to table B. These records may range from 100,000 to 500,000 records.
So my question is that whether is it more feasibile/efficient to use script task to pump all the rows into table B from table A or use OLEDB destionation using sql command. Which is more efficient and help me increase my package performance? Thanks again.
Regards,
Ken
If they are inserts, I'd use the OLEDB Destination to insert directly into the target table. If they are updates, use an OLEDB Destination to write the data to a temp table, then use a Execute SQL task after the data flow to issue a batch update.
sqlScript Table, Index, Stored Procedures from Table list
I have a list of approximately 2000 objects that need to create a sql script
to generate these objects. The script will be applied to another datbase to
generate these objects.
The 2000 objects name have been loaded into a Table A.
Please help put together a program that will read the object names from
Table A and generate a sql script for objects.
Thank You,Option#1
Try from Enterprise Manager (Tools -> Generate SQL Scripts)
Option#2
Try Import\Export utility from Enterprise Manager
Thanks,
RK
"Joe K." wrote:
> I have a list of approximately 2000 objects that need to create a sql scri
pt
> to generate these objects. The script will be applied to another datbase
to
> generate these objects.
> The 2000 objects name have been loaded into a Table A.
> Please help put together a program that will read the object names from
> Table A and generate a sql script for objects.
> Thank You,
>|||I would like to way read from a table to "Generate SQL Scripts".
I would like to automate the "Generate SQL Scripts" procedure.
Thanks,
"Ram Kumar Koditala" wrote:
> Option#1
> Try from Enterprise Manager (Tools -> Generate SQL Scripts)
> Option#2
> Try Import\Export utility from Enterprise Manager
> Thanks,
> RK
> "Joe K." wrote:
>|||You'll have to use SQL DMO to do this. See if the following links help:
http://www.karaszi.com/SQLServer/in...rate_script.asp
http://www.databasejournal.com/feat...cle.php/1480901
Anith
to generate these objects. The script will be applied to another datbase to
generate these objects.
The 2000 objects name have been loaded into a Table A.
Please help put together a program that will read the object names from
Table A and generate a sql script for objects.
Thank You,Option#1
Try from Enterprise Manager (Tools -> Generate SQL Scripts)
Option#2
Try Import\Export utility from Enterprise Manager
Thanks,
RK
"Joe K." wrote:
> I have a list of approximately 2000 objects that need to create a sql scri
pt
> to generate these objects. The script will be applied to another datbase
to
> generate these objects.
> The 2000 objects name have been loaded into a Table A.
> Please help put together a program that will read the object names from
> Table A and generate a sql script for objects.
> Thank You,
>|||I would like to way read from a table to "Generate SQL Scripts".
I would like to automate the "Generate SQL Scripts" procedure.
Thanks,
"Ram Kumar Koditala" wrote:
> Option#1
> Try from Enterprise Manager (Tools -> Generate SQL Scripts)
> Option#2
> Try Import\Export utility from Enterprise Manager
> Thanks,
> RK
> "Joe K." wrote:
>|||You'll have to use SQL DMO to do this. See if the following links help:
http://www.karaszi.com/SQLServer/in...rate_script.asp
http://www.databasejournal.com/feat...cle.php/1480901
Anith
Script Table with secondary indexes
I used the option "Script Table" --> "Create to" in SQL Server Management Studio Express for a table with secondary index (like IX_IndexName), but in the .sql script file there is only the instruction for the primary key and not for the secondary (I expected something like CREATE INDEX). What can I do?
Hi,its not within the context menu, you have to do it via the script wizard which can be found by right clicking on the database and choosing to script the database objects. In the next steps you can switch a boolean to create also the indexes within the table.
HTH, jens Suessmeyer.
http://www.sqlserver2005.de
|||
It work fine, very thanks
Script Table as SELECT To...
I have only recetly started using SQL server 2005 in anger and have
rather amusingly found that Microsoft have fixed something I always
considered a bug but have managed to make it worse. I wonder if maybe
I am missing something.
In Query Analyzer (SQL Server 2000) you were able to right click on a
table and select
Script Object to New Window As..Select
and you would get something like
SELECT [FIELDS] FROM [Table]
except usually you were dealing with a real table so you would get
SELECT [FIELD1], [FIELD2], [FIELD3], [FIELD4], [FIELD5], [FIELD6],
[FIELD7], [FIELD8], [FIELD9], [FIELD10] etc FROM [Table]
In this case, the table name would be off the edge of the screen to
the right. I was in a habit of hitting the End key and hitting CR
before the FROM which pushed the table name onto the second line.
i.e.
SELECT [FIELD1], [FIELD2], etc...
FROM [Table]
I always found this a bit annoying and wished there was a way to
change this behavior.
Now with SQL 2005 and Management Studio, you can right click on a
table and select
Script Table as ... Select To ... New Query Editor Window
and you get something like
SELECT [FIELD1]
, [FIELD2]
, [FIELD3]
, [FIELD4]
, [FIELD5] etc
FROM [Table]
So Microsoft have changed the behavior, but now the table name
dissapears off the bottom of the screen, I find this even MORE
frustrating as I have to scroll down to the bottom to see which table
I am using, also
sometimes I want to have several SELECT statements open, and this
means selecting all the fields and replacing with * or deleting the CR
for each line.
Does anyone have any comments or know of a way of changing the defalt
scripting behavior of Management Studio?<benb@.atwuk.com> wrote in message
news:e92d760a-90fc-4692-b8e8-ce4beacfaff2@.v17g2000hsa.googlegroups.com...
>I have only recetly started using SQL server 2005 in anger and have
> rather amusingly found that Microsoft have fixed something I always
> considered a bug but have managed to make it worse. I wonder if maybe
> I am missing something.
> In Query Analyzer (SQL Server 2000) you were able to right click on a
> table and select
> Script Object to New Window As..Select
> and you would get something like
> SELECT [FIELDS] FROM [Table]
> except usually you were dealing with a real table so you would get
> SELECT [FIELD1], [FIELD2], [FIELD3], [FIELD4], [FIELD5], [FIELD6],
> [FIELD7], [FIELD8], [FIELD9], [FIELD10] etc FROM [Table]
> In this case, the table name would be off the edge of the screen to
> the right. I was in a habit of hitting the End key and hitting CR
> before the FROM which pushed the table name onto the second line.
> i.e.
> SELECT [FIELD1], [FIELD2], etc...
> FROM [Table]
> I always found this a bit annoying and wished there was a way to
> change this behavior.
> Now with SQL 2005 and Management Studio, you can right click on a
> table and select
> Script Table as ... Select To ... New Query Editor Window
> and you get something like
> SELECT [FIELD1]
> , [FIELD2]
> , [FIELD3]
> , [FIELD4]
> , [FIELD5] etc
> FROM [Table]
> So Microsoft have changed the behavior, but now the table name
> dissapears off the bottom of the screen, I find this even MORE
> frustrating as I have to scroll down to the bottom to see which table
> I am using, also
> sometimes I want to have several SELECT statements open, and this
> means selecting all the fields and replacing with * or deleting the CR
> for each line.
> Does anyone have any comments or know of a way of changing the defalt
> scripting behavior of Management Studio?
>
If you drag the Columns node into the editing window it will insert a list
of column names in one line. All you have to do is type SELECT and FROM.
--
David Portas|||On 31 Jan, 19:28, "David Portas"
<REMOVE_BEFORE_REPLYING_dpor...@.acm.org> wrote:
> <b...@.atwuk.com> wrote in message
> news:e92d760a-90fc-4692-b8e8-ce4beacfaff2@.v17g2000hsa.googlegroups.com...
>
>
> >I have only recetly started using SQL server 2005 in anger and have
> > rather amusingly found that Microsoft have fixed something I always
> > considered a bug but have managed to make it worse. I wonder if maybe
> > I am missing something.
> > In Query Analyzer (SQL Server 2000) you were able to right click on a
> > table and select
> > Script Object to New Window As..Select
> > and you would get something like
> > SELECT [FIELDS] FROM [Table]
> > except usually you were dealing with a real table so you would get
> > SELECT [FIELD1], [FIELD2], [FIELD3], [FIELD4], [FIELD5], [FIELD6],
> > [FIELD7], [FIELD8], [FIELD9], [FIELD10] etc FROM [Table]
> > In this case, the table name would be off the edge of the screen to
> > the right. I was in a habit of hitting the End key and hitting CR
> > before the FROM which pushed the table name onto the second line.
> > i.e.
> > SELECT [FIELD1], [FIELD2], etc...
> > FROM [Table]
> > I always found this a bit annoying and wished there was a way to
> > change this behavior.
> > Now with SQL 2005 and Management Studio, you can right click on a
> > table and select
> > Script Table as ... Select To ... New Query Editor Window
> > and you get something like
> > SELECT [FIELD1]
> > =A0 =A0 =A0 =A0 =A0 =A0, [FIELD2]
> > =A0 =A0 =A0 =A0 =A0 =A0, [FIELD3]
> > =A0 =A0 =A0 =A0 =A0 =A0, [FIELD4]
> > =A0 =A0 =A0 =A0 =A0 =A0, [FIELD5] etc
> > FROM [Table]
> > So Microsoft have changed the behavior, but now the table name
> > dissapears off the bottom of the screen, I find this even MORE
> > frustrating as I have to scroll down to the bottom to see which table
> > I am using, also
> > sometimes I want to have several SELECT statements open, and this
> > means selecting all the fields and replacing with * or deleting the CR
> > for each line.
> > Does anyone have any comments or know of a way of changing the defalt
> > scripting behavior of Management Studio?
> If you drag the Columns node into the editing window it will insert a list=
> of column names in one line. All you have to do is type SELECT and FROM.
> --
> David Portas-
So no one knows of any way to customise the scripts generated when
scripting Script Table as .. ?|||<<So no one knows of any way to customise the scripts generated when
scripting Script Table as .. ?>>
AFAIK, no such customization is possible.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<benb@.atwuk.com> wrote in message
news:16aeba87-35c6-4373-902c-12bd5720d132@.e23g2000prf.googlegroups.com...
On 31 Jan, 19:28, "David Portas"
<REMOVE_BEFORE_REPLYING_dpor...@.acm.org> wrote:
> <b...@.atwuk.com> wrote in message
> news:e92d760a-90fc-4692-b8e8-ce4beacfaff2@.v17g2000hsa.googlegroups.com...
>
>
> >I have only recetly started using SQL server 2005 in anger and have
> > rather amusingly found that Microsoft have fixed something I always
> > considered a bug but have managed to make it worse. I wonder if maybe
> > I am missing something.
> > In Query Analyzer (SQL Server 2000) you were able to right click on a
> > table and select
> > Script Object to New Window As..Select
> > and you would get something like
> > SELECT [FIELDS] FROM [Table]
> > except usually you were dealing with a real table so you would get
> > SELECT [FIELD1], [FIELD2], [FIELD3], [FIELD4], [FIELD5], [FIELD6],
> > [FIELD7], [FIELD8], [FIELD9], [FIELD10] etc FROM [Table]
> > In this case, the table name would be off the edge of the screen to
> > the right. I was in a habit of hitting the End key and hitting CR
> > before the FROM which pushed the table name onto the second line.
> > i.e.
> > SELECT [FIELD1], [FIELD2], etc...
> > FROM [Table]
> > I always found this a bit annoying and wished there was a way to
> > change this behavior.
> > Now with SQL 2005 and Management Studio, you can right click on a
> > table and select
> > Script Table as ... Select To ... New Query Editor Window
> > and you get something like
> > SELECT [FIELD1]
> > , [FIELD2]
> > , [FIELD3]
> > , [FIELD4]
> > , [FIELD5] etc
> > FROM [Table]
> > So Microsoft have changed the behavior, but now the table name
> > dissapears off the bottom of the screen, I find this even MORE
> > frustrating as I have to scroll down to the bottom to see which table
> > I am using, also
> > sometimes I want to have several SELECT statements open, and this
> > means selecting all the fields and replacing with * or deleting the CR
> > for each line.
> > Does anyone have any comments or know of a way of changing the defalt
> > scripting behavior of Management Studio?
> If you drag the Columns node into the editing window it will insert a list
> of column names in one line. All you have to do is type SELECT and FROM.
> --
> David Portas-
So no one knows of any way to customise the scripts generated when
scripting Script Table as .. ?
rather amusingly found that Microsoft have fixed something I always
considered a bug but have managed to make it worse. I wonder if maybe
I am missing something.
In Query Analyzer (SQL Server 2000) you were able to right click on a
table and select
Script Object to New Window As..Select
and you would get something like
SELECT [FIELDS] FROM [Table]
except usually you were dealing with a real table so you would get
SELECT [FIELD1], [FIELD2], [FIELD3], [FIELD4], [FIELD5], [FIELD6],
[FIELD7], [FIELD8], [FIELD9], [FIELD10] etc FROM [Table]
In this case, the table name would be off the edge of the screen to
the right. I was in a habit of hitting the End key and hitting CR
before the FROM which pushed the table name onto the second line.
i.e.
SELECT [FIELD1], [FIELD2], etc...
FROM [Table]
I always found this a bit annoying and wished there was a way to
change this behavior.
Now with SQL 2005 and Management Studio, you can right click on a
table and select
Script Table as ... Select To ... New Query Editor Window
and you get something like
SELECT [FIELD1]
, [FIELD2]
, [FIELD3]
, [FIELD4]
, [FIELD5] etc
FROM [Table]
So Microsoft have changed the behavior, but now the table name
dissapears off the bottom of the screen, I find this even MORE
frustrating as I have to scroll down to the bottom to see which table
I am using, also
sometimes I want to have several SELECT statements open, and this
means selecting all the fields and replacing with * or deleting the CR
for each line.
Does anyone have any comments or know of a way of changing the defalt
scripting behavior of Management Studio?<benb@.atwuk.com> wrote in message
news:e92d760a-90fc-4692-b8e8-ce4beacfaff2@.v17g2000hsa.googlegroups.com...
>I have only recetly started using SQL server 2005 in anger and have
> rather amusingly found that Microsoft have fixed something I always
> considered a bug but have managed to make it worse. I wonder if maybe
> I am missing something.
> In Query Analyzer (SQL Server 2000) you were able to right click on a
> table and select
> Script Object to New Window As..Select
> and you would get something like
> SELECT [FIELDS] FROM [Table]
> except usually you were dealing with a real table so you would get
> SELECT [FIELD1], [FIELD2], [FIELD3], [FIELD4], [FIELD5], [FIELD6],
> [FIELD7], [FIELD8], [FIELD9], [FIELD10] etc FROM [Table]
> In this case, the table name would be off the edge of the screen to
> the right. I was in a habit of hitting the End key and hitting CR
> before the FROM which pushed the table name onto the second line.
> i.e.
> SELECT [FIELD1], [FIELD2], etc...
> FROM [Table]
> I always found this a bit annoying and wished there was a way to
> change this behavior.
> Now with SQL 2005 and Management Studio, you can right click on a
> table and select
> Script Table as ... Select To ... New Query Editor Window
> and you get something like
> SELECT [FIELD1]
> , [FIELD2]
> , [FIELD3]
> , [FIELD4]
> , [FIELD5] etc
> FROM [Table]
> So Microsoft have changed the behavior, but now the table name
> dissapears off the bottom of the screen, I find this even MORE
> frustrating as I have to scroll down to the bottom to see which table
> I am using, also
> sometimes I want to have several SELECT statements open, and this
> means selecting all the fields and replacing with * or deleting the CR
> for each line.
> Does anyone have any comments or know of a way of changing the defalt
> scripting behavior of Management Studio?
>
If you drag the Columns node into the editing window it will insert a list
of column names in one line. All you have to do is type SELECT and FROM.
--
David Portas|||On 31 Jan, 19:28, "David Portas"
<REMOVE_BEFORE_REPLYING_dpor...@.acm.org> wrote:
> <b...@.atwuk.com> wrote in message
> news:e92d760a-90fc-4692-b8e8-ce4beacfaff2@.v17g2000hsa.googlegroups.com...
>
>
> >I have only recetly started using SQL server 2005 in anger and have
> > rather amusingly found that Microsoft have fixed something I always
> > considered a bug but have managed to make it worse. I wonder if maybe
> > I am missing something.
> > In Query Analyzer (SQL Server 2000) you were able to right click on a
> > table and select
> > Script Object to New Window As..Select
> > and you would get something like
> > SELECT [FIELDS] FROM [Table]
> > except usually you were dealing with a real table so you would get
> > SELECT [FIELD1], [FIELD2], [FIELD3], [FIELD4], [FIELD5], [FIELD6],
> > [FIELD7], [FIELD8], [FIELD9], [FIELD10] etc FROM [Table]
> > In this case, the table name would be off the edge of the screen to
> > the right. I was in a habit of hitting the End key and hitting CR
> > before the FROM which pushed the table name onto the second line.
> > i.e.
> > SELECT [FIELD1], [FIELD2], etc...
> > FROM [Table]
> > I always found this a bit annoying and wished there was a way to
> > change this behavior.
> > Now with SQL 2005 and Management Studio, you can right click on a
> > table and select
> > Script Table as ... Select To ... New Query Editor Window
> > and you get something like
> > SELECT [FIELD1]
> > =A0 =A0 =A0 =A0 =A0 =A0, [FIELD2]
> > =A0 =A0 =A0 =A0 =A0 =A0, [FIELD3]
> > =A0 =A0 =A0 =A0 =A0 =A0, [FIELD4]
> > =A0 =A0 =A0 =A0 =A0 =A0, [FIELD5] etc
> > FROM [Table]
> > So Microsoft have changed the behavior, but now the table name
> > dissapears off the bottom of the screen, I find this even MORE
> > frustrating as I have to scroll down to the bottom to see which table
> > I am using, also
> > sometimes I want to have several SELECT statements open, and this
> > means selecting all the fields and replacing with * or deleting the CR
> > for each line.
> > Does anyone have any comments or know of a way of changing the defalt
> > scripting behavior of Management Studio?
> If you drag the Columns node into the editing window it will insert a list=
> of column names in one line. All you have to do is type SELECT and FROM.
> --
> David Portas-
So no one knows of any way to customise the scripts generated when
scripting Script Table as .. ?|||<<So no one knows of any way to customise the scripts generated when
scripting Script Table as .. ?>>
AFAIK, no such customization is possible.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<benb@.atwuk.com> wrote in message
news:16aeba87-35c6-4373-902c-12bd5720d132@.e23g2000prf.googlegroups.com...
On 31 Jan, 19:28, "David Portas"
<REMOVE_BEFORE_REPLYING_dpor...@.acm.org> wrote:
> <b...@.atwuk.com> wrote in message
> news:e92d760a-90fc-4692-b8e8-ce4beacfaff2@.v17g2000hsa.googlegroups.com...
>
>
> >I have only recetly started using SQL server 2005 in anger and have
> > rather amusingly found that Microsoft have fixed something I always
> > considered a bug but have managed to make it worse. I wonder if maybe
> > I am missing something.
> > In Query Analyzer (SQL Server 2000) you were able to right click on a
> > table and select
> > Script Object to New Window As..Select
> > and you would get something like
> > SELECT [FIELDS] FROM [Table]
> > except usually you were dealing with a real table so you would get
> > SELECT [FIELD1], [FIELD2], [FIELD3], [FIELD4], [FIELD5], [FIELD6],
> > [FIELD7], [FIELD8], [FIELD9], [FIELD10] etc FROM [Table]
> > In this case, the table name would be off the edge of the screen to
> > the right. I was in a habit of hitting the End key and hitting CR
> > before the FROM which pushed the table name onto the second line.
> > i.e.
> > SELECT [FIELD1], [FIELD2], etc...
> > FROM [Table]
> > I always found this a bit annoying and wished there was a way to
> > change this behavior.
> > Now with SQL 2005 and Management Studio, you can right click on a
> > table and select
> > Script Table as ... Select To ... New Query Editor Window
> > and you get something like
> > SELECT [FIELD1]
> > , [FIELD2]
> > , [FIELD3]
> > , [FIELD4]
> > , [FIELD5] etc
> > FROM [Table]
> > So Microsoft have changed the behavior, but now the table name
> > dissapears off the bottom of the screen, I find this even MORE
> > frustrating as I have to scroll down to the bottom to see which table
> > I am using, also
> > sometimes I want to have several SELECT statements open, and this
> > means selecting all the fields and replacing with * or deleting the CR
> > for each line.
> > Does anyone have any comments or know of a way of changing the defalt
> > scripting behavior of Management Studio?
> If you drag the Columns node into the editing window it will insert a list
> of column names in one line. All you have to do is type SELECT and FROM.
> --
> David Portas-
So no one knows of any way to customise the scripts generated when
scripting Script Table as .. ?
Tuesday, March 20, 2012
Script SP with permission automatically
Hi,
In enterprise manager, there is a option to automatically script any trigger, permission on a table or store procedure. It seems that this feature is gone from new the SQL management studio. Is there a way to easy script this easily just like before?
thanks,
Bernie
Hi there,Well, you can script triggers by going to the specific table in the Object Explorer and expanding the node for that table....There should be a sub-node for triggers which you can expand to see the triggers defined on the table. Right click on the desired trigger and select "Script Trigger As" from the context menu that appears.
Note that you can take the same action for most of the other sub-nodes you see under the table such as Constraints, Indexes and Keys.
As for scripting objects with permissions, I believe that you will need to use the Script Wizard to do this. To access the wizard you right click on a database and from the context menu that appears you select: Tasks > Generate Scripts
The Script Wizard will now appear. You can run through it as follows:
1) Click the "Next>" button on the initial screen
2) Select the database you want to script and then click the "Next>" button. You can also tick the "Script All Objects In The Selected Database" option if you want to script absolutely everything
3) You will be prompted to select scripting options such as scripting object permissions, table triggers and constraints etc. Review the options and select the ones that are right for you
4) If you did not select the "Script All Objects In The Selected Database" option, you will now get to choose which database objects (e.g. stored procedures) to script and will run through a series of screens in order to make your selection
It's pretty much smooth sailing after that.
Hope that helps a bit, but sorry if it doesn't
|||
sql server management studio 2005 has a better scripting support than sql server enterprise manager 2000.
you can right click the object and the choose "script object to ; alter ; create ; delete;"
yet another way to do it is to right click the object and then choose properties
on the top portion of the properties tab there's a clcikable script dropdown which allows you to script to clipboard, file, jobs or to new query window. on the left hand side of the properties window there's a listbox with the following item geneneral,permission, extended properties. to script permission, choose permission from the list and then click the script dropdown
here's another
You can also right click the database then task then choose then choose generate scripts. this will lunch the scripts wizard which is somewaht similar to those of the EM
Script out whole SQL SERVER Database including INSERT
Hi All,
I am trying to script out a whole SQL SERVER database with all objects
in it along with INSERT Statments to populate data into the table. My
goal is to have one single file which I can just run and produce the
same structure in a new system.
I do not know if there is a way to do that. If there is, I would really
appreciate if any of you can let me know.
Thanks
-- CCDon=B4t know which SQL Server version you are using but for SQL2k5 the
new namespace from SSIS offers something to do that programmatically.
Anyway, if you don=B4t want the tools and want to do that the old
fashioned way, there is a script from vyaskn which produces the
statement from TSQL:
http://vyaskn.tripod.com/code.htm#inserts
HTH, Jens Suessmeyer.
I am trying to script out a whole SQL SERVER database with all objects
in it along with INSERT Statments to populate data into the table. My
goal is to have one single file which I can just run and produce the
same structure in a new system.
I do not know if there is a way to do that. If there is, I would really
appreciate if any of you can let me know.
Thanks
-- CCDon=B4t know which SQL Server version you are using but for SQL2k5 the
new namespace from SSIS offers something to do that programmatically.
Anyway, if you don=B4t want the tools and want to do that the old
fashioned way, there is a script from vyaskn which produces the
statement from TSQL:
http://vyaskn.tripod.com/code.htm#inserts
HTH, Jens Suessmeyer.
script out table definition and data
Is there any free download tool that I could use to script out table
definition along with the data?
There is a tool by vyas but it was not scripting out correctly. I don't mean
to say wrong things about the tool but may be it was "my bad"!!
When I used that tool, it was chopping out some of the characters at the
very end of script lines.
Please let me know if there is any other free tool out there that I could us
e.
ThanksThat was most likely due to the setting of the # of characters returned per
column in Query Analyzer. Change that setting "Tools - Options" and you
will most likely get the desired results. But note you will always be
limited to 8000 bytes per column in SQL 2000 QA.
Andrew J. Kelly SQL MVP
"sqlster" <nospam@.nospam.com> wrote in message
news:14C21109-D426-468A-A321-C28150271812@.microsoft.com...
> Is there any free download tool that I could use to script out table
> definition along with the data?
> There is a tool by vyas but it was not scripting out correctly. I don't
> mean
> to say wrong things about the tool but may be it was "my bad"!!
> When I used that tool, it was chopping out some of the characters at the
> very end of script lines.
> Please let me know if there is any other free tool out there that I could
> use.
> Thanks|||I'm not familiar with the tool but if you're using Query Analyzer to execute
it, you'll need to change the output results setting.
Tools | Options. Click Results tab. Set the Maximum number of characters
per column to 8192 (the max).
Just a thought,
Joe
"sqlster" wrote:
> Is there any free download tool that I could use to script out table
> definition along with the data?
> There is a tool by vyas but it was not scripting out correctly. I don't me
an
> to say wrong things about the tool but may be it was "my bad"!!
> When I used that tool, it was chopping out some of the characters at the
> very end of script lines.
> Please let me know if there is any other free tool out there that I could
use.
> Thanks|||Joe and Andrew,
Thank you very much and it works great.
I knew it was "my bad" !!!
"Joe from WI" wrote:
> I'm not familiar with the tool but if you're using Query Analyzer to execu
te
> it, you'll need to change the output results setting.
> Tools | Options. Click Results tab. Set the Maximum number of characters
> per column to 8192 (the max).
> Just a thought,
> Joe
> "sqlster" wrote:
>
definition along with the data?
There is a tool by vyas but it was not scripting out correctly. I don't mean
to say wrong things about the tool but may be it was "my bad"!!
When I used that tool, it was chopping out some of the characters at the
very end of script lines.
Please let me know if there is any other free tool out there that I could us
e.
ThanksThat was most likely due to the setting of the # of characters returned per
column in Query Analyzer. Change that setting "Tools - Options" and you
will most likely get the desired results. But note you will always be
limited to 8000 bytes per column in SQL 2000 QA.
Andrew J. Kelly SQL MVP
"sqlster" <nospam@.nospam.com> wrote in message
news:14C21109-D426-468A-A321-C28150271812@.microsoft.com...
> Is there any free download tool that I could use to script out table
> definition along with the data?
> There is a tool by vyas but it was not scripting out correctly. I don't
> mean
> to say wrong things about the tool but may be it was "my bad"!!
> When I used that tool, it was chopping out some of the characters at the
> very end of script lines.
> Please let me know if there is any other free tool out there that I could
> use.
> Thanks|||I'm not familiar with the tool but if you're using Query Analyzer to execute
it, you'll need to change the output results setting.
Tools | Options. Click Results tab. Set the Maximum number of characters
per column to 8192 (the max).
Just a thought,
Joe
"sqlster" wrote:
> Is there any free download tool that I could use to script out table
> definition along with the data?
> There is a tool by vyas but it was not scripting out correctly. I don't me
an
> to say wrong things about the tool but may be it was "my bad"!!
> When I used that tool, it was chopping out some of the characters at the
> very end of script lines.
> Please let me know if there is any other free tool out there that I could
use.
> Thanks|||Joe and Andrew,
Thank you very much and it works great.
I knew it was "my bad" !!!
"Joe from WI" wrote:
> I'm not familiar with the tool but if you're using Query Analyzer to execu
te
> it, you'll need to change the output results setting.
> Tools | Options. Click Results tab. Set the Maximum number of characters
> per column to 8192 (the max).
> Just a thought,
> Joe
> "sqlster" wrote:
>
Script out "Trigger rules"
When I choose the tables, generatel SQL script. it insist me to generate the
"create table" script also.
Any Simple method which i can script trigger rule (for all tables) only ?
ThxIf I understand your question...
In the Enterprise manager, goto the formatting tab and uncheck Generate Crea
te and Drop for the Objects.
Then on the Options tab check Triggers.
That should gen just the Trigger scripts.
Matt Barbour
Technology Specialist – Communications Sector.
mbarbour@.microsoft.com
W: 469-775-6206
C: 972-746-1278
--Original Message--
From: Agnes
Posted At: Friday, October 28, 2005 10:32 AM
Posted To: microsoft.public.sqlserver.programming
Conversation: Script out "Trigger rules"
Subject: Script out "Trigger rules"
When I choose the tables, generatel SQL script. it insist me to generate the
"create table" script also.
Any Simple method which i can script trigger rule (for all tables) only ?
Thx|||Yes, I try that,It will generate "CREATE TABLE...." SCRIPT together ,
IF I unclick "create table script", and tick "Trigger script" only . Nothing
is generated.
"Matt Barbour" <mestolphies@.hotmail.com> glsD:uTc8cd92FHA.3188@.TK2MSFTNGP12.phx.g
bl...
> If I understand your question...
> In the Enterprise manager, goto the formatting tab and uncheck Generate
> Create and Drop for the Objects.
> Then on the Options tab check Triggers.
> That should gen just the Trigger scripts.
> Matt Barbour
> Technology Specialist 'Communications Sector.
> mbarbour@.microsoft.com
> W: 469-775-6206
> C: 972-746-1278
> --Original Message--
> From: Agnes
> Posted At: Friday, October 28, 2005 10:32 AM
> Posted To: microsoft.public.sqlserver.programming
> Conversation: Script out "Trigger rules"
> Subject: Script out "Trigger rules"
>
> When I choose the tables, generatel SQL script. it insist me to generate
> the
> "create table" script also.
> Any Simple method which i can script trigger rule (for all tables) only ?
> Thx|||Interesting.. never noticed that before.
Is this something you are going to do more then once?
If not, just do the Create flag + the Trigger flag and prune off all the Cre
ate table's, they are up front prior to the first trigger command.
--Original Message--
From: Agnes
Posted At: Friday, October 28, 2005 10:57 AM
Posted To: microsoft.public.sqlserver.programming
Conversation: Script out "Trigger rules"
Subject: Re: Script out "Trigger rules"
Yes, I try that,It will generate "CREATE TABLE...." SCRIPT together ,
IF I unclick "create table script", and tick "Trigger script" only . Nothing
is generated.
"Matt Barbour" <mestolphies@.hotmail.com> glsD:uTc8cd92FHA.3188@.TK2MSFTNGP12.phx.gbl...[colo
r=darkred]
> If I understand your question...
> In the Enterprise manager, goto the formatting tab and uncheck Generate
> Create and Drop for the Objects.
> Then on the Options tab check Triggers.
> That should gen just the Trigger scripts.
> - Matt.
> --Original Message--
> From: Agnes
> Posted At: Friday, October 28, 2005 10:32 AM
> Posted To: microsoft.public.sqlserver.programming
> Conversation: Script out "Trigger rules"
> Subject: Script out "Trigger rules"
>
> When I choose the tables, generatel SQL script. it insist me to generate
> the
> "create table" script also.
> Any Simple method which i can script trigger rule (for all tables) only ?
> Thx[/color]|||Are you perhaps still trying to temporarily disable triggers? Read the
answers to your previous posts.
ML|||Agnes,
You may wish to try to script just the trigger with
sp_helptext tr_mytrigger
HTH
JeffP...
"Agnes" <agnes@.dynamictech.com.hk> wrote in message
news:%23royOT92FHA.2800@.TK2MSFTNGP10.phx.gbl...
> When I choose the tables, generatel SQL script. it insist me to generate t
he
> "create table" script also.
> Any Simple method which i can script trigger rule (for all tables) only ?
> Thx
>
>
"create table" script also.
Any Simple method which i can script trigger rule (for all tables) only ?
ThxIf I understand your question...
In the Enterprise manager, goto the formatting tab and uncheck Generate Crea
te and Drop for the Objects.
Then on the Options tab check Triggers.
That should gen just the Trigger scripts.
Matt Barbour
Technology Specialist – Communications Sector.
mbarbour@.microsoft.com
W: 469-775-6206
C: 972-746-1278
--Original Message--
From: Agnes
Posted At: Friday, October 28, 2005 10:32 AM
Posted To: microsoft.public.sqlserver.programming
Conversation: Script out "Trigger rules"
Subject: Script out "Trigger rules"
When I choose the tables, generatel SQL script. it insist me to generate the
"create table" script also.
Any Simple method which i can script trigger rule (for all tables) only ?
Thx|||Yes, I try that,It will generate "CREATE TABLE...." SCRIPT together ,
IF I unclick "create table script", and tick "Trigger script" only . Nothing
is generated.
"Matt Barbour" <mestolphies@.hotmail.com> glsD:uTc8cd92FHA.3188@.TK2MSFTNGP12.phx.g
bl...
> If I understand your question...
> In the Enterprise manager, goto the formatting tab and uncheck Generate
> Create and Drop for the Objects.
> Then on the Options tab check Triggers.
> That should gen just the Trigger scripts.
> Matt Barbour
> Technology Specialist 'Communications Sector.
> mbarbour@.microsoft.com
> W: 469-775-6206
> C: 972-746-1278
> --Original Message--
> From: Agnes
> Posted At: Friday, October 28, 2005 10:32 AM
> Posted To: microsoft.public.sqlserver.programming
> Conversation: Script out "Trigger rules"
> Subject: Script out "Trigger rules"
>
> When I choose the tables, generatel SQL script. it insist me to generate
> the
> "create table" script also.
> Any Simple method which i can script trigger rule (for all tables) only ?
> Thx|||Interesting.. never noticed that before.
Is this something you are going to do more then once?
If not, just do the Create flag + the Trigger flag and prune off all the Cre
ate table's, they are up front prior to the first trigger command.
--Original Message--
From: Agnes
Posted At: Friday, October 28, 2005 10:57 AM
Posted To: microsoft.public.sqlserver.programming
Conversation: Script out "Trigger rules"
Subject: Re: Script out "Trigger rules"
Yes, I try that,It will generate "CREATE TABLE...." SCRIPT together ,
IF I unclick "create table script", and tick "Trigger script" only . Nothing
is generated.
"Matt Barbour" <mestolphies@.hotmail.com> glsD:uTc8cd92FHA.3188@.TK2MSFTNGP12.phx.gbl...[colo
r=darkred]
> If I understand your question...
> In the Enterprise manager, goto the formatting tab and uncheck Generate
> Create and Drop for the Objects.
> Then on the Options tab check Triggers.
> That should gen just the Trigger scripts.
> - Matt.
> --Original Message--
> From: Agnes
> Posted At: Friday, October 28, 2005 10:32 AM
> Posted To: microsoft.public.sqlserver.programming
> Conversation: Script out "Trigger rules"
> Subject: Script out "Trigger rules"
>
> When I choose the tables, generatel SQL script. it insist me to generate
> the
> "create table" script also.
> Any Simple method which i can script trigger rule (for all tables) only ?
> Thx[/color]|||Are you perhaps still trying to temporarily disable triggers? Read the
answers to your previous posts.
ML|||Agnes,
You may wish to try to script just the trigger with
sp_helptext tr_mytrigger
HTH
JeffP...
"Agnes" <agnes@.dynamictech.com.hk> wrote in message
news:%23royOT92FHA.2800@.TK2MSFTNGP10.phx.gbl...
> When I choose the tables, generatel SQL script. it insist me to generate t
he
> "create table" script also.
> Any Simple method which i can script trigger rule (for all tables) only ?
> Thx
>
>
Monday, March 12, 2012
script index
i have four indexes on a table.
i was wondering if anybody can tell me how to script an existing index?
i know how to do this in 2000...wondering how to do in 2005
thanks
? Hi RookieDBA, In Query Analyzer: 1. Hit F8 to open the object explorer; 2. Locate correct database; go to User Tables, find right table and drill down to "Indexes" 3. Right-click Index; choose Script Object to New Window As / Create -- Hugo Kornelis, SQL Server MVP <RookieDBA@.discussions..microsoft.com> schreef in bericht news:ec51452a-622b-48dc-aa2b-dc2ebb7db970@.discussions.microsoft.com... i have four indexes on a table. i was wondering if anybody can tell me how to script an existing index? i.e the sql used to create the index thanks
Script generation for Objects - Management Studio
How do I generate a single script for each object - table, view, index, trigger, sp, function etc.... in SQL Server. The script that is generated from SQL Server wizards is in a single file.
I want to have separate file for each of the objects.
In EM, you right-click on the object, select 'all tasks', then 'Generate SQL Script'. I do not have access to Management Studio right now, so this may or may not be much help.
Do you have access to Enterprise Manager? You can run SQL Server 2000 and 2005 side by side, so it may be worthwhile installing if you cannot do this in MS.
Clarity Consulting (http://www.claritycon.com)
|||Well we have the same feature in SQL 2005 but I need one individual file for each object. I do not want the script of say all tables in file. I want this in different files with file name as table name that is getting scripted.
It applies to other objects like procs, funcs, triggers, constraints, FKs etc
|||You might look at Scriptio. I use it to create .sql files off of my db schema. On the 2nd tab I select ONE FILE PER OBJECT. www.sqlteam.com. The only problem I have found is that existing sql files (we re-exporting) can't be read-only.|||Currently, the Generate Script Wizard can only create a single file. We plan to implement file-per-object functionality in SP2.
Subscribe to:
Posts (Atom)