DECLARE @ObjectName sysname
DECLARE @Stmt varchar(256)
SET @ObjectName = 'dbo.stbTable01'

IF exists (select * from sysobjects 
	where id = object_id(@ObjectName) 
	and ObjectProperty(id,'IsTable')=1)
BEGIN
   SET @Stmt = 'DROP TABLE '+@ObjectName
   EXEC(@Stmt)
   IF exists (select * from sysobjects where id = object_id(@ObjectName) 
 		and ObjectProperty(id,'IsTable')=1)
   BEGIN
	RAISERROR('<<FAILED TO DROP TABLE %s>>',16,1,@ObjectName)
   END
   ELSE 
	RAISERROR('<<DROPPED TABLE %s>>',0,1,@ObjectName)
END

go

CREATE TABLE [dbo].[stbTable01]
(
	[siTableID]	[smallint]		NOT NULL,
	[vcDesc]	[varchar](100)	NOT NULL,
	[vcCode]	[varchar](100)	NOT NULL,	
 CONSTRAINT [XPKstbTable01] PRIMARY KEY CLUSTERED 
(
	[siTableID] ASC
) ON [fgStaticData]
) ON [fgStaticData]

GO


DECLARE @ObjectName sysname
declare @Stmt varchar(256)
set @ObjectName = 'dbo.stbTable01'

IF exists (select * from sysobjects where id = object_id(@ObjectName) 
	   and ObjectProperty(id,'IsTable')=1)
BEGIN
	RAISERROR('<<CREATED TABLE %s>>',0,1,@ObjectName)
END
ELSE 
BEGIN
  RAISERROR('<<FAILED TO CREATE TABLE %s>>',16,1,@ObjectName)
END
GO
Grant insert, select, update, delete on dbo.stbTable01 to AMSRole
GO

-- Add extended properties for TABLE: stbTable01
EXEC sys.sp_addextendedproperty 
@name=N'Description', 
@value=N'This table holds xxxxxxxxxx' ,
@level0type=N'SCHEMA', 
@level0name=N'dbo', --Schema Name
@level1type=N'TABLE', 
@level1name=N'stbTable01'--Table Name 
GO
Last modified: May 21, 2021

Author

Comments

Write a Reply or Comment