Path: csiph.com!x330-a1.tempe.blueboxinc.net!usenet.pasdenom.info!weretis.net!feeder1.news.weretis.net!news.swapon.de!eternal-september.org!feeder.eternal-september.org!.POSTED!not-for-mail From: Erland Sommarskog Newsgroups: comp.databases.ms-sqlserver Subject: Re: Temp Variables in MS SQL Scripts Date: Wed, 07 Sep 2011 23:05:09 +0200 Organization: Erland Sommarskog Lines: 50 Message-ID: References: Mime-Version: 1.0 Content-Type: text/plain; charset=windows-1252 Content-Transfer-Encoding: 8bit Injection-Info: mx04.eternal-september.org; posting-host="5slhep4ZgM2dUe3EsTsNxQ"; logging-data="16916"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX18l8v3PGXXXH/CqapJw50pP" User-Agent: Xnews/2006.08.24 Mime-proxy/2.1.c.0 (Win32) Cancel-Lock: sha1:IopAs86gx61IqlntORoX6MeJjsU= Xref: x330-a1.tempe.blueboxinc.net comp.databases.ms-sqlserver:649 BrianDP (bdp222@gmail.com) writes: > This is a script that the sql front end client wrote for me,- it > inserts a field toward the top of the layout for an autonumber/ > Autoincrement field. I would like to know if you can replace the name > Personnl1 toward the top with a variable name and replace the variable > down into the script, so I wouldnt' have to type that name over and > over. Your table design looks funny. Save for the IDENTITY column all columns are nullable. What would a NULL value in EMPID signify? In LNAME? In FNAME? Here is a tip: if you don't know what a NULL for a column would mean, you should probably make that column NOT NULL. Keep in mind that if you make a column nullable, this also means that you need to add code to handle the NULL values. As for your question, you can change: > EXEC sys.sp_addextendedproperty @name=N'AllowZeroLength', > @value=N'False' , @level0type=N'SCHEMA',@level0name=N'dbo', > @level1type=N'TABLE',@level1name=N'PERSONNL1B', > @level2type=N'COLUMN',@level2name=N'EMPID' > GO To DECLARE @tablename sysname SELECT @tablename 'PERSONNL1B' EXEC sys.sp_addextendedproperty @name=N'AllowZeroLength', @value=N'False' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=@tablename, @level2type=N'COLUMN',@level2name=N'EMPID' Note here that I have deleted the GO. The GO is an instruction to Mgmt Studio to send the text to SQL Server as a indepdent batch, and variables exitsts only for the batch. Thus, you must remove all GO. This does not cover the CREATE TABLE statement; you cannot have a parameter for the name there. Nor does it make any sense, since a table is supposed to describe a unique entity. Thus the likelihood that two tables have the definition is slim. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx