CODEWITHSUNDEEP
sql-server
Henrov
Henrov

Reputation: 1610

Get tablename from function

I am creating tables. The tablenames are created dynamically.

Now the analysts say they need a field called 'dataset' that contains the name of the table.

Long story why they want it, apparently it is a deal killer if it's not in there.

So I was thinking of adding a computed column along the lines of 

create table test
(
    id int identity(1,1),
    field1 nvarchar(256) null,
    dataset as table_name()   --<--  this is what I am looking for
)

I know of the functions DB_USER(), @@SERVERNAME and so on, is there something like that for the table? For example CURRENT_TABLE()?

Since I create the tables dynamically it is quite easy to add the column and fill it during creation but this question popped up and it keeps bugging me.

Upvotes: 0

Views: 201

Answers (3)

Henrov
Henrov

Reputation: 1610

Not surprisingly, it seems there is no function that returns a table_name. So I did what I normally do in such a case, create the tabel dynamically using sp_executesql and a string that I build, including c2 = @varTable_name.

declare @table_name as nvarchar(1000) = 'mytable'
declare @sql as nvarchar(1000) = 'create table ' + @table_name + '(
c1 INT NOT NULL IDENTITY(1,1),
c2 AS ''' + @table_name +''');'
exec sp_executesql @sql

I was just wondering if there was a function that I could use instead of the var @table_name

Upvotes: 1

TT.
TT.

Reputation: 16146

There is no such function. There is a way to achieve your objective using a computed column though. Like this:

CREATE TABLE ttt(
    c1 INT NOT NULL IDENTITY(1,1),
    c2 AS 'ttt'
);
INSERT INTO ttt DEFAULT VALUES;
SELECT*FROM ttt;

c2 is a computed column that just returns a constant, that being the table name.

Upvotes: 1

B.Muthamizhselvi
B.Muthamizhselvi

Reputation: 642

In dynamic table creation, table name from one string. you can use same string to assign the table name to dataset value using default constraint. 

create table test(
id int identity(1,1)
,field1  nvarchar(256) NULL
,dataset varchar(255) default  'test' 
)

Upvotes: 1

Related Questions