SQL Server Performance Forum – Threads Archive

Executing a text file against currenct conn

I have written a dynamic script to recreate tables on the fly with desired changes to its schema without affecting the constraints and indexes. 1) This script is backward compatible to SQL 6.5 and thus has a limitation of only allowing variables to be defined of size 255. I could export information to a file much in the same way "All Tasks/Generate SQL Script…" works from Enterprise Manager. …But how could I execute this against the current connection without having to hard-code in the user name and password. i.e. xp_cmdshell ‘isql -S …….. file path and name…..’ 2) Anyone know a way of dynamically retrieving information required to generate triggers that exist on a table? Regards,
Rik

---

Create a batch file for the same and execute the query using isql. For this bath file u can pass password as command line parameter. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

---

What do u mean by dynamically retrieving information? Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

---

for 2
do you want to recreate the same exact triggers? IF so, you should be able to use the same sprocs that MS does (just change them to get what you want).
If you want to create new triggers based just on pk/fk relationships, then you should be able to use the sysdepends table. But you may miss out on any custom triggers that way. Chris

---

This is an old example, I do not have the amended one with me that works with 2k, but essentially it gets schemas for desired tables.
Do you know of a more simplistic way of doing this. i.e. Is there any documentation on the Microsoft SQL Stored Procedures that appear on the Master database? /*
Author : Richard Beacroft
Created: 2003
Description:Acts to dynamically re-create a table, with all indexes and constraints intact
allowing you to amend its schema as desired. (This may have an effect on schema re-creation).
Dependencies: zs_maint_tableintegrity.sql
Supports: SQL 6.5
Key:*** Represents areas most likely to amend according to your needs.
*/
GO
SET NOCOUNT ON
GO
create table #_parameters
(default_user varchar(10) not null,
table_prefix varchar(5) not null,
old_renamed varchar(5) not null) insert into #_parameters
values (‘dbo’, ‘_’, ‘old_’)
GO
PRINT ‘DROP TEMPORARY TABLES FOR BUILDING STATEMENTS TOT EXECUTE’
if exists (select * from sysobjects where id = object_id(‘dbo._cpm_TableDefinitions’) and sysstat & 0xf = 3)
drop table _cpm_TableDefinitions
GO
if exists (select * from sysobjects where id = object_id(‘dbo._cpm_TableCreation’) and sysstat & 0xf = 3)
drop table _cpm_TableCreation
GO
PRINT ‘CREATE TEMPORARY TABLES FOR BUILDING STATEMENTS TOT EXECUTE’
GO
CREATE TABLE _cpm_TableDefinitions
(tableid int identity(1,1) not null,
user_name varchar(10) null, — values applied later
table_name varchar(50) null, — values applied later
col_name varchar(50) not null,
col_id int not null,
DataType varchar(30) not null,
col_len int not null,
col_prec int null,
col_scale int null,
BaseType varchar(30) not null,
col_defname varchar(50) null,
col_rulname varchar(50) null,
col_null int not null,
col_identity int not null,
col_flags int not null,
col_seed int null,
col_increment int null,
col_dridefname varchar(30) null,
col_drideftext varchar(100) null
)
GO
CREATE TABLE _cpm_TableCreation
(table_id int identity(1,1) not null,
OLD_table_name varchar(50) not null,
NEW_table_name varchar(50) not null,
create_statement varchar(50) null
)
GO
PRINT ‘CORRECT ALL SYSTEM TABLE INDEXES IF OUT OF SYNC’
EXEC sp_MSforeachtable @command1="print ‘?’ DBCC DBREINDEX (‘?’)"
GO
PRINT ‘POPULATE TABLE WITH TABLE SCHEMA FOR DESIRED TABLES’
DECLARE @table_name VARCHAR(50)
DECLARE @user_name VARCHAR(10)
DECLARE @fq_table_name VARCHAR(50) SELECT @user_name = default_user from #_parameters — USER PERMISSIONS DECLARE Table_Cursor CURSOR FOR
(SELECT DISTINCT o.Name
FROM SysObjects o
JOIN SysColumns c ON (o.ID = c.ID)
WHERE o.Type = ‘u’
and o.Name IN (‘tablename1’, ‘tablename2’)) — *** LIST ALL TABLES YOU WANT A SCHEMA CREATED FOR
ORDER BY o.Name OPEN Table_Cursor FETCH NEXT FROM Table_Cursor INTO @table_name
WHILE @@FETCH_STATUS = 0
BEGIN
— ALL WB TABLES ARE DBO..
SELECT @fq_table_name = @user_name + ‘.’ + @table_name insert into _cpm_TableDefinitions
(col_name, col_id, DataType, col_len, col_prec, col_scale, BaseType, col_defname, col_rulname,
col_null, col_identity, col_flags, col_seed, col_increment, col_dridefname, col_drideftext)
exec sp_MShelpcolumns @fq_table_name, 512, @orderby = ‘id’ update _cpm_TableDefinitions
set user_name = @user_name,
table_name = @table_name
where tableid >= (select min(tableid) from _cpm_TableDefinitions (nolock) where table_name is null) FETCH NEXT FROM Table_Cursor INTO @table_name
END CLOSE Table_Cursor
DEALLOCATE Table_Cursor
GO
/*
*** Amend table "_cpm_TableDefinitions" here to contain new definitions for desired tables
*/
GO
PRINT ‘POPULATE TABLE _cpm_TableCreation WITH CREATE TABLE STATEMENTS USING RETRIEVED TABLE SCHEMAS’
DECLARE @CREATE_STATEMENTvarchar(255)
DECLARE @INSERT_STATEMENT varchar(255)
DECLARE @prev_table_name varchar(50)
DECLARE @tableidint
DECLARE @table_name varchar(50)
DECLARE @col_name varchar(50)
DECLARE @col_id int
DECLARE @DataType varchar(30)
DECLARE @col_len int
DECLARE @col_prec int
DECLARE @col_scale int
DECLARE @BaseType varchar(30)
DECLARE @col_defname varchar(50)
DECLARE @col_rulname varchar(50)
DECLARE @col_null int
DECLARE @col_identity int
DECLARE @col_flags int
DECLARE @col_seed int
DECLARE @col_increment int
DECLARE @col_dridefname varchar(30)
DECLARE @col_drideftextvarchar(100)
DECLARE @user_name varchar(10)
DECLARE @fq_table_name varchar(50)
DECLARE @fq_orig_table_name varchar(50)
DECLARE @table_name_prefixvarchar(5) select @table_name_prefix = table_prefix
from #_parameters DECLARE CreateStatement_Cursor CURSOR FOR
(SELECT *
FROM _cpm_TableDefinitions)
ORDER BY table_name, col_id OPEN CreateStatement_Cursor FETCH NEXT FROM CreateStatement_Cursor INTO @tableid, @user_name, @table_name, @col_name, @col_id, @DataType, @col_len,
@col_prec, @col_scale, @BaseType, @col_defname, @col_rulname, @col_null,
@col_identity, @col_flags, @col_seed, @col_increment, @col_dridefname,
@col_drideftext
WHILE @@FETCH_STATUS = 0
BEGIN
select @fq_table_name = @user_name + ‘.’ + @table_name_prefix + @table_name,
@fq_orig_table_name = @user_name + ‘.’ + @table_name — SELECT * FROM _cpm_TableDefinitions WHERE DATATYPE <> BASETYPE — 0 Rows Returned
if (@prev_table_name <> @table_name OR @prev_table_name IS NULL)
begin
IF (NOT @CREATE_STATEMENT IS NULL) — DO NOT PERFORM ON FIRST CALL
BEGIN
update _cpm_TableCreation
set create_statement = substring(create_statement, 1, LEN(create_statement) – 3) + ‘)’
from_cpm_TableCreation
where table_id = (select max(table_id) from _cpm_TableCreation)
END select @prev_table_name = @table_name SELECT @CREATE_STATEMENT = ‘CREATE TABLE ‘ + @fq_table_name + ‘(‘
INSERT INTO _cpm_TableCreation (OLD_table_name, NEW_table_name, create_statement)
VALUES (@fq_orig_table_name, @fq_table_name, @CREATE_STATEMENT)
end
SELECT @CREATE_STATEMENT = @col_name + ‘ ‘ +
@DataType +
CASE @DataType WHEN ‘varchar’THEN ‘(‘ + convert(varchar(15), @col_len) + ‘)’
WHEN ‘char’THEN ‘(‘ + convert(varchar(15), @col_len) + ‘)’ END +
CASE @col_null WHEN 0 THEN ‘ NOT NULL,’ ELSE ‘ NULL,’ END
INSERT INTO _cpm_TableCreation (OLD_table_name, NEW_table_name, create_statement)
VALUES (@fq_orig_table_name, @fq_table_name, @CREATE_STATEMENT) — check number of fields — FETCH NEXT FROM CreateStatement_Cursor INTO @tableid, @user_name, @table_name, @col_name, @col_id, @DataType, @col_len,
@col_prec, @col_scale, @BaseType, @col_defname, @col_rulname, @col_null,
@col_identity, @col_flags, @col_seed, @col_increment, @col_dridefname,
@col_drideftext END update _cpm_TableCreation
set create_statement = substring(create_statement, 1, len(create_statement) – 1) + ‘)’
from_cpm_TableCreation
where table_id = (select max(table_id) from _cpm_TableCreation) CLOSE CreateStatement_Cursor
DEALLOCATE CreateStatement_Cursor GO
PRINT ‘CREATE A COPY OF THE DESIRED TABLES, WITH A TABLE NAME PREFIXED WITH AN "_"’
DECLARE @CREATE_STATEMENT1varchar(255)
DECLARE @CREATE_STATEMENT2varchar(255)
DECLARE @CREATE_STATEMENT3varchar(255)
DECLARE @CREATE_STATEMENT4varchar(255)
DECLARE @CREATE_STATEMENT5varchar(255)
DECLARE @create_statement varchar(50)
DECLARE @prev_table_name varchar(50)
DECLARE @old_table_name varchar(50)
DECLARE @new_table_namevarchar(50)
DECLARE @old_renamed varchar(50)
DECLARE @ERRORint
DECLARE @row_counterint select @old_renamed = old_renamed
from #_parameters IF EXISTS (SELECT OLD_table_name
FROM _cpm_TableCreation
GROUP BY OLD_table_name
HAVING count(OLD_table_name) > 25) — ONLY ENOUGH CREATE STATEMENTS TO HANDLE 25 ROWS
BEGIN
PRINT ‘MSG: TABLE DEFINITION IS GREATER THAN 25 LINES. THIS IS NOT CURRENTLY HANDLED’
GOTO ABORT
END DECLARE CreateStatement_Cursor CURSOR FOR
(SELECT OLD_table_name,
NEW_table_name,
create_statement
FROM _cpm_TableCreation)
ORDER BY table_id OPEN CreateStatement_Cursor FETCH NEXT FROM CreateStatement_Cursor INTO @old_table_name, @new_table_name, @create_statement
WHILE @@FETCH_STATUS = 0
BEGIN
if (@prev_table_name <> @new_table_name OR @prev_table_name IS NULL)
begin
IF (NOT @prev_table_name IS NULL)
BEGIN
— CREATE TABLE STATEMENT
exec (@CREATE_STATEMENT1 + @CREATE_STATEMENT2 + @CREATE_STATEMENT3 + @CREATE_STATEMENT4 + @CREATE_STATEMENT5)
if @@error > 0
begin
SELECT ‘MSG: CREATE STATEMENT FAILED TO EXECUTE FOR TABLE: ‘ + @new_table_name
end
else
begin
exec (‘insert into ‘ + @new_table_name + ‘ select * from ‘ + @old_table_name)
if @@error > 0
begin
SELECT ‘MSG: INSERT STATEMENT FAILED TO EXECUTE FOR TABLE: ‘ + @new_table_name
end
else
begin
exec @ERROR = xsp_integrity @old_table_name, 0
if @@error > 0
begin
SELECT ‘MSG: FAILED TO DROP INDEXES AND CONSTRAINTS ON TABLE: ‘ + @old_table_name
end
else
begin
select @old_renamed = @old_renamed + @old_table_name
exec sp_rename @old_table_name, @old_renamed, OBJECT
if @@error > 0
begin
SELECT ‘MSG: FAILED TO RENAME THE TABLE: ‘ + @old_table_name + ‘ TO: ‘ + @old_renamed
end
else
begin
exec sp_rename @new_table_name, @old_table_name, OBJECT
if @@error > 0
begin
SELECT ‘MSG: FAILED TO RENAME THE TABLE: ‘ + @new_table_name + ‘ TO: ‘ + @old_table_name
end
else
begin
exec @ERROR = xsp_integrity @old_table_name, 1
if @@error > 0
begin
SELECT ‘MSG: FAILED TO CREATE INDEXES AND CONSTRAINTS ON TABLE: ‘ + @new_table_name + ‘ TO: ‘ + @old_table_name
end
end
end
end
end
end
END select @prev_table_name = @new_table_name
select @CREATE_STATEMENT1 = ”
select @CREATE_STATEMENT2 = ”
select @CREATE_STATEMENT3 = ”
select @CREATE_STATEMENT4 = ”
select @CREATE_STATEMENT5 = ”
select @row_counter = 0
end
select @row_counter = @row_counter + 1
if (@row_counter <= 5)
select @CREATE_STATEMENT1 = @CREATE_STATEMENT1 + ltrim(rtrim(@create_statement))
if (@row_counter > 5 and @row_counter <= 10)
select @CREATE_STATEMENT2 = @CREATE_STATEMENT2 + ltrim(rtrim(@create_statement))
if (@row_counter > 10 and @row_counter <= 15)
select @CREATE_STATEMENT3 = @CREATE_STATEMENT3 + ltrim(rtrim(@create_statement))
if (@row_counter > 15 and @row_counter <= 20)
select @CREATE_STATEMENT4 = @CREATE_STATEMENT4 + ltrim(rtrim(@create_statement))
if (@row_counter > 20 and @row_counter <= 25)
select @CREATE_STATEMENT5 = @CREATE_STATEMENT5 + ltrim(rtrim(@create_statement)) FETCH NEXT FROM CreateStatement_Cursor INTO @old_table_name, @new_table_name, @create_statement
END CLOSE CreateStatement_Cursor
DEALLOCATE CreateStatement_Cursor IF (NOT @prev_table_name IS NULL)
BEGIN
— CREATE TABLE STATEMENT
exec (@CREATE_STATEMENT1 + @CREATE_STATEMENT2 + @CREATE_STATEMENT3 + @CREATE_STATEMENT4 + @CREATE_STATEMENT5)
if @@error > 0
begin
SELECT ‘MSG: CREATE STATEMENT FAILED TO EXECUTE FOR TABLE: ‘ + @new_table_name
end
else
begin
exec (‘insert into ‘ + @new_table_name + ‘ select * from ‘ + @old_table_name)
if @@error > 0
begin
SELECT ‘MSG: INSERT STATEMENT FAILED TO EXECUTE FOR TABLE: ‘ + @new_table_name
end
else
begin
exec @ERROR = xsp_integrity @old_table_name, 0
if @@error > 0
begin
SELECT ‘MSG: FAILED TO DROP INDEXES AND CONSTRAINTS ON TABLE: ‘ + @old_table_name
end
else
begin
select @old_renamed = @old_renamed + @old_table_name
exec sp_rename @old_table_name, @old_renamed, OBJECT
if @@error > 0
begin
SELECT ‘MSG: FAILED TO RENAME THE TABLE: ‘ + @old_table_name + ‘ TO: ‘ + @old_renamed
end
else
begin
exec sp_rename @new_table_name, @old_table_name, OBJECT
if @@error > 0
begin
SELECT ‘MSG: FAILED TO RENAME THE TABLE: ‘ + @new_table_name + ‘ TO: ‘ + @old_table_name
end
else
begin
exec @ERROR = xsp_integrity @old_table_name, 1
if @@error > 0
begin
SELECT ‘MSG: FAILED TO CREATE INDEXES AND CONSTRAINTS ON TABLE: ‘ + @new_table_name + ‘ TO: ‘ + @old_table_name
end
end
end
end
end
end
END ABORT:
GO
drop table #_parameters
GO
SET NOCOUNT OFF

---

]]>