How to query all fields in a table except for a few fields

The landlord just started not to know how to check, and then began to Baidu

Most of the netizens are suggested one to write of course programmers are the most lazy, one to write how painful ah

Then find

Not to mention a lot of Daniel's in Csdn.

So this script appears.

Declare @s nvarchar( +)
Select@s=IsNull(@s+',',"')+QuoteName(Name) fromsyscolumnswhereId=object_id('Table name') andName notinch('Exclude Fields')Order byColid
exec('Select'+@s+'From table name')

After execution, wow, perfect is exactly what I want.

Then Bo Master idle and down turned over there is another netizen also gave a similar method code as follows:

Get all the columns in the table except Col1, Col2
For example: USERNO_FM, userno_to
CreateTableTest
NumintIdentity(1,1),
Userno_fmvarchar(Ten),
Userno_tovarchar(Ten),
Usernamevarchar(Ten))
Select* fromTest

Declare@sqlvarchar(8000)
Select@sql="'
Select@sql=@sql+','+[name] from
(Select[name] fromsyscolumnswhereobject_id(N'[Test]')=[ID] and[name] notinch('userno_fm','userno_to')) A

Set@sql='Select'+Stuff(@sql,1,1,"')+'From [test]'
--Print @sql
exec(@sql)

DropTableTest

Execute the result is the same

You can also see the direct difference between the two methods

The first one is perfect

The second one is a little awkward because

Select@sql="' Select@sql=@sql+','+[name] fromcan be used@s=IsNull(@s+',',"') instead

'Select'+Stuff(@sql,1,1,"') thanQuoteName(Name) facilitates direct

But the second one is the blogger's writing style, so the blogger has a personal feeling. See the first code like a turtle and a goddess

Today once again feel the beauty of programming, code of the beauty of the heart of a breeze blowing cool

Bloggers also have a new conjecture, in the future, directly using SQL to query the JSON-formatted data will not be in the backend third-party tools to convert the table

Code quoted in this article: http://bbs.csdn.net/topics/330143955#quote

How to query all fields in a table except for a few fields