CODEWITHSUNDEEP
sql-servervariables
Arash
Arash

Reputation: 1826

Use variable instead of schema Eg dbo. for creating table

I would like to use variables instead of schema name in at create time for tables or views.

Eg instead of 

create table dbo.TableName

I want to

create table @schema.TableName

Also I would like to write below statement with the mentioned solution.

IF OBJECT_ID (N'dbo.TableName', 'u') IS NOT NULL
    DROP table TableName;
go

Is this way totally possible or not, if it's possible which way is more efficient.

Upvotes: 3

Views: 13179

Answers (2)

granadaCoder
granadaCoder

Reputation: 27904

You need to research "sqlcmd" mode.

http://msdn.microsoft.com/en-us/library/ms174187.aspx

Example. Note, in SSMS, Under "Query" there is a "SqlCmd Mode" menu item you need to enable.

:setvar MySchemaName "dbo"


IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = N'$(MySchemaName)' and TABLE_NAME = N'Ticket' and TABLE_TYPE = N'BASE TABLE' ) 
BEGIN 
DROP TABLE [$(MySchemaName)].[Ticket] 
END 
GO


CREATE TABLE [$(MySchemaName)].[Ticket] ( 
    [TicketUUID] [uniqueidentifier] NOT NULL,
    [CreateDate] [datetime] NOT NULL DEFAULT CURRENT_TIMESTAMP
    )

GO


SELECT * FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = N'$(MySchemaName)' and TABLE_NAME = N'Ticket' and TABLE_TYPE = N'BASE TABLE'

Upvotes: 3

Caffé
Caffé

Reputation: 1171

You can build your statement concatenating objects names and use sp_executesql to submit it to the server:

Using sp_executesql

For example, creating:

DECLARE @SQLString NVARCHAR(500);
DECLARE @TableName NVARCHAR(100);

SET @TableName = 'dbo.TableName';
SET @SQLString = 'CREATE TABLE ' +  @TableName + ' ...';

EXECUTE sp_executesql @SQLString;

and dropping:

SET @TableName = 'dbo.TableName';
SET @SQLString = 'IF OBJECT_ID (''' + @TableName + ''', ''u'') IS NOT NULL DROP table ' + @TableName;

EXECUTE sp_executesql @SQLString;

You'll have to mitigate the "Sql Injection" risk if you are getting the object names from the user.

Upvotes: 3

Related Questions