[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 474
  • Last Modified:

SQL Select of tables

Is there a select statement I can use that selects * from @tblName where (column <> unique identifier column)
0
lrbrister
Asked:
lrbrister
  • 6
  • 4
  • 2
  • +5
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
could you please clarify what exactly you are trying to solve as problem?
0
 
radcaesarCommented:
Yes, You can use comparison operator in Unique Identifiers but no arithmetic operators.
0
 
Paul JacksonCommented:
So you want to select all columns from a table except if the column is of datatype uniqueindentifier.

I'm afraid I don't think this is possible.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Om PrakashCommented:
You can write dynamic SQL statements as :

DECLARE @SQL VARCHAR(2000);
SET @SQL = 'select * from ' + @tblName  + 'where column_name  <> ' + @column_name
EXEC(@SQL)

Where @column_name & @tblName   are variables..
0
 
Rajkumar GsSoftware EngineerCommented:
If you mean, you want to pass table name as a parameter, you need to use dynamic query.

DECLARE @SQL VARCHAR(MAX)
SET @SQL = 'selects * from ' + @tblName + ' where (column <> unique identifier column) '
EXEC @SQL

Open in new window

0
 
lrbristerAuthor Commented:
angelll

I am building a Web Page with an excel button and a list of tables that they can do an export on.

When they select the table I want to do a select * on that table and give them everything but the UID column.
0
 
lrbristerAuthor Commented:
Hey guys...I know how to do the dynamic sql etc.

I'm just trying to ALWAYS exclude the primary key column...they have several names throughout these tables.  (id, dealID, eventsID...etc...)
0
 
TSSTJeffCommented:
SELECT 'SELECT ' || array_to_string(ARRAY(SELECT 'o' || '.' || c.column_name
        FROM information_schema.columns As c
            WHERE table_name = (your table name)
            AND  c.column_name NOT IN('officeparkid', 'contractor')
    ), ',') || ' FROM (your table name)As o' As sqlstmt


This should work for you.
0
 
lrbristerAuthor Commented:
TSSTJeff:
Did you try that in SQL?  Needs tons of modifications
0
 
TSSTJeffCommented:
you have to customize it for your table set but that is the concept.
0
 
lrbristerAuthor Commented:
TSSTJeff,
 I did...and got this error message
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '|'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '|'.


On this change
SELECT  || array_to_string(ARRAY(SELECT 'o' || '.' || c.column_name
        FROM information_schema.columns As c
            WHERE table_name = r_users
            AND  c.column_name NOT IN('officeparkid', 'contractor')
    ), ',') || ' FROM (r_users)As o' As sqlstmt
0
 
TSSTJeffCommented:
select c.column_name from information_schema.columns as c
             where TABLE_NAME='Employee' and c.COLUMN_NAME not in ('eid')
             
there a working version
change the 'eid' to the name of the indexed column and 'Employee' to your table name "Keep the single quotes' and you should be good
0
 
lrbristerAuthor Commented:
So this really goes back to  jacko72:...that it isn't possible to exclude column by "column type"

The ecluded column has to be set to = or an "in" statement with all uid's in ths database.
0
 
TSSTJeffCommented:
no  not at all
select * from information_schema.columns
if you look at this select statement you will see a column that tells you what the column type is so you could adjust the statement to look at column_type instead of name
0
 
LCSandman8301Commented:
if you primary key constraints are all defined and use a naming convention like 'PK_'
you can query the information_schema.key_column_usage for the specified table for the column name
declare @tablename varchar(1000)

set @tablename = 'Applications'

select column_name
from information_schema.columns
where table_name = @tablename
and column_name not in (
	select column_name
	from information_schema.key_column_usage
	where table_name = @tablename
	and constraint_name like 'PK_%'
)

Open in new window

0
 
LCSandman8301Commented:
and if your primary keys don't conform to a naming convention the below code would work better.
the downside here is the user you are connecting to the database with would require access to the information_schema views.
declare @tablename varchar(1000)

set @tablename = 'Applications'

select column_name
from information_schema.columns
where table_name = @tablename
and column_name not in (
	select column_name
	from information_schema.key_column_usage
	where table_name = @tablename
	and constraint_name in (
		select constraint_name
		from information_schema.table_constraints
		where table_name = @tablename
		and constraint_type = 'PRIMARY KEY'
	)
)

Open in new window

0
 
lrbristerAuthor Commented:
Great!  I can do a join on those and exclude the PK column from the select * from statement
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 6
  • 4
  • 2
  • +5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now