select table name - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

select table name

Author

Topic

arifpratama
Starting Member

1 Post

Posted - 2015-03-19 : 08:20:34

DECLARE

@name nvarchar(max),
@sql nvarchar(max),
@column nvarchar(max)

DECLARE cur_files CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR
select t.name,c.name from sys.tables t
inner join sys.columns c on t.object_id = c.object_id
where c.name = 'date'

open cur_files;
FETCH NEXT FROM cur_files INTO @name, @column
WHILE @@FETCH_STATUS = 0
begin
set @sql = 'select top 1 cast(date as date)Date,count(1)Count from '+@name+'
group by cast(date as date)
order by cast(date as date) desc
'
execute(@sql);

FETCH NEXT FROM cur_files
INTO @name, @column
END
close cur_files
DEALLOCATE cur_files

please help me
how to select table name on this query??
i want to select table name from the cursor

select top 1 table_name cast(date as date)Date,count(1)Count from '+@name+'
group by cast(date as date)
order by cast(date as date) desc

Kristen
Test

22859 Posts

Posted - 2015-03-19 : 09:01:46

This perhaps?


set @sql = 'select top 1 '''+@name+''' AS MyTableName, cast(date as date)Date,count(1)Count from '+@name+'
group by cast(date as date)
order by cast(date as date) desc
'

But you need to guard against the Table Name (or any other parameter you include like this) containing a single-quote !!!! otherwise you are SERIOUSLY at risk from SQL Injection.

A Table Name containing a space would also prevent your query working (surround the table name with "[" and "]" - but then you need to safeguard to make sure it doesn't contain those characters ...) Look into using QUOTENAME for those safeguards

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-19 : 09:04:31

use this in your dynamic sql:


set @sql = 'select top 1 ''' + @name+ ''' as Table_Name, cast(date as date)Date,count(1)Count from '+@name+'
group by cast(date as date)
order by cast(date as date) desc
'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2015-03-19 : 12:52:38

and make sure to read this too www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources