Follow along with the video below to see how to install our site as a web app on your home screen.
Note: This feature may not be available in some browsers.
declare @tbl table (table_name varchar(255),column_name varchar(255),id int identity(1,1))
insert into @tbl
select table_name, column_name from information_schema.[columns] Where Table_Name = 'Tblname'
declare @int int
declare @cols varchar(3000)
declare @cmd varchar(4000)
set @cols = ''
set @int = 1
while @int <= (select count(*) from information_schema.[columns] Where Table_Name = 'Tblname')
begin
if (select column_name from @tbl where id = @int) <> 'Col1'
Begin
set @cols = @cols + (select column_name from @tbl where id = @int) + ','
End
set @int = @int + 1
end
If (Len(@cols) > 0)
set @cmd = 'Select ' + SubString(@cols,1,Len(@cols)-1) + ' From Tblname'
Exec(@cmd)
[COLOR=blue]use[/color] northwind
[COLOR=blue]declare[/color] @tablename [COLOR=blue]varchar[/color](100) [COLOR=green]--table name
[/color][COLOR=blue]declare[/color] @columnexclude [COLOR=blue]varchar[/color](1000)
[COLOR=blue]set[/color] @tablename = [COLOR=red]'customers'[/color]
[COLOR=blue]set[/color] @columnexclude = [COLOR=red]'phone, fax'[/color]
[COLOR=green]--this will hold all columns in table
[/color][COLOR=blue]declare[/color] @t [COLOR=blue]table[/color] (ID [COLOR=blue]int[/color] [COLOR=blue]identity[/color](1,1), colname [COLOR=blue]varchar[/color](100))
[COLOR=green]--fill it with column list
[/color][COLOR=blue]insert[/color] @t
[COLOR=blue]select[/color] foo.name [COLOR=blue]from[/color] syscolumns foo
[COLOR=blue]inner[/color] [COLOR=blue]join[/color] ([COLOR=blue]select[/color] * [COLOR=blue]from[/color] sysobjects [COLOR=blue]where[/color] [COLOR=blue]name[/color] = @tablename and xtype = [COLOR=red]'U'[/color]) bar
[COLOR=blue]on[/color] foo.id = bar.id
[COLOR=green]--remove columns you don't want from list
[/color][COLOR=blue]delete[/color] @t
[COLOR=blue]where[/color] colname in ([COLOR=blue]select[/color] [COLOR=blue]value[/color] [COLOR=blue]from[/color] test.dbo.Split(@columnexclude, [COLOR=red]','[/color]))
[COLOR=blue]declare[/color] @fooq nvarchar(4000)
[COLOR=blue]set[/color] @fooq = [COLOR=red]'select '[/color]
[COLOR=blue]declare[/color] @i [COLOR=blue]int[/color]
[COLOR=blue]set[/color] @i = 1
[COLOR=blue]while[/color] @i < ([COLOR=blue]select[/color] [COLOR=#FF00FF]max[/color](ID) [COLOR=blue]from[/color] @t)
[COLOR=blue]begin[/color]
[COLOR=blue]select[/color] @fooq = @fooq + colname + [COLOR=red]','[/color]
[COLOR=blue]from[/color] @t
[COLOR=blue]where[/color] ID = @i
[COLOR=blue]set[/color] @i = @i + 1
[COLOR=blue]end[/color]
[COLOR=blue]set[/color] @fooq = [COLOR=#FF00FF]left[/color](@fooq, len(@fooq) - 1) + [COLOR=red]' from customers'[/color]
[COLOR=blue]print[/color] @fooq
[COLOR=blue]execute[/color] sp_executesql @fooq
[COLOR=blue]create[/color] [COLOR=blue]proc[/color] fooProc(@tablename [COLOR=blue]varchar[/color](100), @columnexclude [COLOR=blue]varchar[/color](500))
[COLOR=blue]as[/color]
[COLOR=green]--this will hold all columns in table
[/color][COLOR=blue]declare[/color] @t [COLOR=blue]table[/color] (ID [COLOR=blue]int[/color] [COLOR=blue]identity[/color](1,1), colname [COLOR=blue]varchar[/color](100))
[COLOR=green]--fill it with column list
[/color][COLOR=blue]insert[/color] @t
[COLOR=blue]select[/color] foo.name [COLOR=blue]from[/color] syscolumns foo
[COLOR=blue]inner[/color] [COLOR=blue]join[/color] ([COLOR=blue]select[/color] * [COLOR=blue]from[/color] sysobjects [COLOR=blue]where[/color] [COLOR=blue]name[/color] = @tablename and xtype = [COLOR=red]'U'[/color]) bar
[COLOR=blue]on[/color] foo.id = bar.id
[COLOR=green]--remove columns you don't want from list
[/color][COLOR=blue]delete[/color] @t
[COLOR=blue]where[/color] colname in ([COLOR=blue]select[/color] [COLOR=blue]value[/color] [COLOR=blue]from[/color] test.dbo.Split(@columnexclude, [COLOR=red]','[/color]))
[COLOR=green]--string to contain query
[/color][COLOR=blue]declare[/color] @fooq nvarchar(4000)
[COLOR=blue]set[/color] @fooq = [COLOR=red]'select '[/color]
[COLOR=green]--integer for while loop
[/color][COLOR=blue]declare[/color] @i [COLOR=blue]int[/color]
[COLOR=blue]set[/color] @i = 1
[COLOR=green]--while loop
[/color][COLOR=blue]while[/color] @i < ([COLOR=blue]select[/color] [COLOR=#FF00FF]max[/color](ID) [COLOR=blue]from[/color] @t)
[COLOR=blue]begin[/color]
[COLOR=green]--add column name to query
[/color] [COLOR=blue]select[/color] @fooq = @fooq + colname + [COLOR=red]','[/color]
[COLOR=blue]from[/color] @t
[COLOR=blue]where[/color] ID = @i
[COLOR=green]--increment loop counter
[/color] [COLOR=blue]set[/color] @i = @i + 1
[COLOR=blue]end[/color]
[COLOR=green]--trim the extra column and add table name to select from
[/color][COLOR=blue]set[/color] @fooq = [COLOR=#FF00FF]left[/color](@fooq, len(@fooq) - 1) + [COLOR=red]' from '[/color] + @tablename
[COLOR=green]--execute the constructed query
[/color][COLOR=blue]execute[/color] sp_executesql @fooq