How to Validate Tables in Dynamic SQL

Posted on 2008-11-12
Last Modified: 2012-05-05
This is for homework, the reading & provided notes  are exteremly light. one of the homework problems requires me to pass a column and table varchar to a procedure.

for which i have....
create proc proc_test
@col varchar(max),
@table varchar(max)
declare @sql_string varchar(max)

set @sql_string = 'select ' + @col + ' from ' + @table;
exec (@sql_string);

Now, the next part of the assignment has me validating the @table variable is in fact a table within the database and not some inserted code.(delete, drop, insert, etc.)

I changed the @table to a sysname variable but that does not seem right to me.

Any insight is appreciated.....or a go here read this would be grand.thanks
Question by:cflores89
    LVL 9

    Expert Comment

    You could use systems views in sql 2005 and 2008
    or SELECT name FROM dbo.sysobjects WHERE xtype = 'U'  see code

    IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(@table) AND type = 'D')
    ....your code 

    Open in new window

    LVL 9

    Accepted Solution

    Using the dbo.sysobjects the  type should be 'U' and not 'D', please see code.
    You have other methods to list tables within the database like
    declare @table nvarchar(250)
    set @table='List1'
    SELECT * FROM information_schema.Tables
    Where Table_Name =@table

    SELECT * FROM sys.Tables
    Where Name =@table

    declare @table nvarchar(max)
    set @table='dbo.List1'
    IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(@table) AND type in ('U'))
    ---Your code

    Open in new window


    Author Closing Comment

    Perfect!  I actually use teh schema.Tables suggestion

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
    When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
    Via a live example, show how to shrink a transaction log file down to a reasonable size.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now