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.

Help!

-Brian
a


USE [Personnel]
GO

/****** Object: Table [dbo].[PERSONNL1] Script Date: 09/07/2011
12:27:06 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
/* CREATE TABLE Persons (P_Id int NOT NULL AUTO_INCREMENT, */
/* job_id smallint IDENTITY(1,1)PRIMARY KEY CLUSTERED, */

CREATE TABLE [dbo].[PERSONNL1B](
[AutoID] [smallint] IDENTITY(1,1)PRIMARY KEY CLUSTERED NOT NULL,
[EMPID] [nvarchar](6) NULL,
[EMPNMBR] [nvarchar](3) NULL,
[LNAME] [nvarchar](15) NULL,
[FNAME] [nvarchar](15) NULL,

BrianDP (bdp222 (AT) gmail (DOT) com) writes:
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:

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 (AT) sommarskog (DOT) 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

Let me add to Erland's comment that, in general, if
you find yourself creating multiple tables which
are identical except for the name you can replace
them with a single table with an added dimension
(field in the primary key or unique constraint) to
take care of whatever distinction you were making
with the table name.

This not only easier to create initially, but saves
you from having to create a new table and modify
maintenance procedures every time a new
value in this dimension comes up.

Fred.