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.
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?