Using variables and wild card in a SQL String

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

Using variables and wild card in a SQL String

Author

Topic

magictech
Starting Member

44 Posts

Posted - 2004-11-04 : 16:41:28

I'm trying to put together a script that would alter a table and add NOT FOR REPLICATION to the identity column on several hundred tables. Since I am unable to write a complex script to automate the entire process, I've decided to do the update one table at a time. Here is a script that I'm working on. I'm getting an error message when I try to run the script. Can someone please show me how to properly use variable with a wild card in a string ? or how to properly add this variables in this script?

DECLARE @Var1 AS CHAR(50), @tbn AS CHAR(50)
SELECT @tbn = 'tablename'
SELECT @Var1 = Name FROM SysIndexes WHERE Name LIKE 'pk%' AND Name LIKE '%' + @tbn + '%'
EXECUTE('ALTER TABLE [@tbn] DROP CONSTRAINT ' + @Var1
ALTER TABLE + @tbn + DROP COLUMN mkey
ALTER TABLE + @tbn + ADD viewkey INT IDENTITY (1,1) NOT FOR REPLICATION NOT NULL
ALTER TABLE + @tbn + ADD CONSTRAINT PK_+ @tbn PRIMARY KEY (mkey) WITH FILLFACTOR=90 ON [PRIMARY]

Thanks in advance

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-04 : 16:47:53

Perhaps the error message you are getting might be useful ????

- Jeff

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-11-04 : 17:21:23

*lol*
'And how' + @cynical + 'was that'

rockmoose

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-04 : 17:27:46

WHERE Name LIKE 'pk%' AND OBJECT_NAME(id) = @tbn

Tara

magictech
Starting Member

44 Posts

Posted - 2004-11-04 : 17:51:07

Server: Msg 170, Level 15, State 1, Line 7
Line 7: Incorrect syntax near '+'.

Here is the error message I'm getting. This script should work if I remove all the variables I'm trying to incorporate in order to make the script more dynamic.

---Top section of the script. Here I declare a variable to hole the table name. Set the variable to the tablename.

DECLARE @Var1 AS CHAR(50), @tbn AS CHAR(50)
SELECT @tbn = 'XXX'
print @tbn
SELECT @Var1 = Name FROM SysIndexes WHERE Name LIKE 'pk%' AND Name LIKE % @tbn %
print @Var1

--this section of the script would alter a given table.
EXECUTE('ALTER TABLE [@tbn] DROP CONSTRAINT ' + @Var1)
ALTER TABLE [xxx] DROP COLUMN columnname
ALTER TABLE [xxx] ADD columnname INT IDENTITY (1,1) NOT FOR REPLICATION NOT NULL
ALTER TABLE [xxx] ADD CONSTRAINT PK_VAN PRIMARY KEY (columnname) WITH FILLFACTOR=90 ON [PRIMARY]

Any help with the syntax would be greatly appreciated.

Thanks in advance

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-04 : 17:57:33

Do you realize that this script will add the column to the end of the table? Not that order matters, but you may want it at the top. To do that, you are going to have to rebuild the tables by creating the table with a different name using the exact ddl that you want, then moving your data to this new table, then drop the source, renaming the new table to your tablename, then adding back constraints and everything else.

And please see my response in the other thread about the NOT FOR REPLICATION option.

Tara

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-11-04 : 17:59:23

You can't use a variable for tablename:
~~ALTER TABLE + @tbn + DROP COLUMN mkey~~
EXEC( 'ALTER TABLE ' + @tbn + ' DROP COLUMN mkey' )

rockmoose

Kristen
Test

22859 Posts

Posted - 2004-11-05 : 01:55:24

[code]
EXECUTE('ALTER TABLE [@tbn] DROP CONSTRAINT ' + @Var1 + '
ALTER TABLE' + @tbn + 'DROP COLUMN mkey
ALTER TABLE' + @tbn + 'ADD viewkey INT IDENTITY (1,1) NOT FOR REPLICATION NOT NULL
ALTER TABLE' + @tbn + 'ADD CONSTRAINT PK_' + @tbn + 'PRIMARY KEY (mkey) WITH FILLFACTOR=90 ON [PRIMARY]')
[/code]
But I would suggest concatenating into a declared variable first - then you can SELECT it to see how it looks, then EXECUTE (@strSQL)

Kristen

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-11-05 : 02:27:02

for tables you can...

declare @tblNames table(cnt int identity(1,1),tblName varchar(100))
declare @curr int, @last int

insert into @tblNames(tblname)
select table_name from information_schema.tables
where patindex('dt%',table_name)=0
and table_type='base table'

set @last=@@rowcount+1
set @curr=1

while @curr<@last
begin
...whatever code you have, to get tablename, just
'select tblname from @tblnames
where cnt=@curr'

set @curr=@curr+1 --increment

end

--edit
you can even include the column name in the table, use information_schema.columns

--------------------
keeping it simple...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources