Sql return table information by username

I would like to receive all table information for the submited tablename in a stored procedure.

For example something like this :

create proc selectUserTable 
@username nvarchar(30)
as
begin
select * from @username
end

Of course this doesn't work, but how can I make something like that?

If I do this:

create proc selectUserTable 
@username nvarchar(30)
    as
    begin
SELECT * FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME LIKE '@username'
end

It will only return the tablename not the information in it.

Answers


You need dynamic query, but be aware of sql injection:

create proc selectUserTable 
@username nvarchar(30)
as
begin
  Exec('select * from ' +  @username)
end

If @tablename = 'users; drop table users;', you will be in trouble. Better first get objectid by tablename variable then again get objectname by objectid and pass it to exec function, rather then passing tablename variable directly.


create proc createTable
@username
as
begin
  exec ('create table '+@username + '(

  id int,
  ritnaam varchar(50),
  naam varchar(50)
  )')
end

Something like this?


Need Your Help

How to catch all exceptions except a specific one?

java exception

Is it possible to catch all exceptions of a method, except for a specific one, which should be thrown?

How to use cross validation in MATLAB

matlab machine-learning svm cross-validation

I'm trying to make a svm classificator using Matlab and want to use cross validation. But