Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Любителям писать 'select * ...'  [new]
invm
Member

Откуда: Москва
Сообщений: 9229
Предлагается фукция, позволяющая выводить список столбцов таблицы по шаблону:
create function dbo.ufn_GetTableColumnList
	(
		@TableName sysname,
		@Template varchar(255),
		@Delimeter varchar(255),
		@UseBracketing int
	)
returns nvarchar(max)
as
begin

/*
  Template:
   %c - column name
   %t - column datatype
*/

 declare @result nvarchar(max), @TableCatalog sysname, @TableOwner sysname

 declare @Column nvarchar(255)
 
 select
  @TableCatalog = db_name(),
  @TableOwner = isnull(parsename(@TableName, 2/*Имя схемы*/), N'dbo'),
  @TableName = parsename(@TableName, 1/*Имя объекта*/)

 declare columns cursor local read_only for
  select
   replace(
    replace(
     @Template,
     '%c',
     case when isnull(@UseBracketing, 0) <> 0 then quotename(COLUMN_NAME) else COLUMN_NAME end
    ),
    '%t',
    isnull(
     case when isnull(@UseBracketing, 0) <> 0 then quotename(DOMAIN_NAME) else DOMAIN_NAME end,
     case when isnull(@UseBracketing, 0) <> 0 then quotename(DATA_TYPE) else DATA_TYPE end +
      case
       when DATA_TYPE in ('char', 'nchar', 'varchar', 'nvarchar', 'binary', 'varbinary') then
        '(' + case when CHARACTER_MAXIMUM_LENGTH = -1 then 'max' else convert(varchar(10), CHARACTER_MAXIMUM_LENGTH) end + ')'
       when DATA_TYPE in ('numeric', 'decimal') then
        '(' + convert(varchar(10), NUMERIC_PRECISION) + ',' + convert(varchar(10), NUMERIC_SCALE) + ')'
       else ''
      end
    )
   )
  from
   INFORMATION_SCHEMA.COLUMNS
  where
   TABLE_CATALOG = @TableCatalog and
   TABLE_SCHEMA = @TableOwner and
   TABLE_NAME = @TableName
  order by
   ORDINAL_POSITION

 select
  @result = ''

 open columns
 fetch next from columns into @Column
 while @@fetch_status = 0
  begin
   select @result = @result + case when @result = '' then '' else @Delimeter end + @Column
   fetch next from columns into @Column
  end
 close columns
 deallocate columns

 return @result
end
Примеры использования:
select ufn_GetTableColumnList('Table', '%c', ', ', 0)
select ufn_GetTableColumnList('Table', 'declare @%c %t', ', ', 0)
7 сен 09, 14:59    [7627008]     Ответить | Цитировать Сообщить модератору
 Re: Любителям писать 'select * ...'  [new]
Любитель писать select *
Guest
invm,
Ну и как Ваша функция соотносится с "любовью писать select *"???
7 сен 09, 15:02    [7627022]     Ответить | Цитировать Сообщить модератору
 Re: Любителям писать 'select * ...'  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
а если пользовательский тип данных?

для спящего время бодрствования равносильно сну
7 сен 09, 15:13    [7627112]     Ответить | Цитировать Сообщить модератору
 Re: Любителям писать 'select * ...'  [new]
x-x
Member

Откуда:
Сообщений: 230
invm
Предлагается фукция, позволяющая выводить список столбцов таблицы по шаблону:
+

create function dbo.ufn_GetTableColumnList
	(
		@TableName sysname,
		@Template varchar(255),
		@Delimeter varchar(255),
		@UseBracketing int
	)
returns nvarchar(max)
as
begin

/*
  Template:
   %c - column name
   %t - column datatype
*/

 declare @result nvarchar(max), @TableCatalog sysname, @TableOwner sysname

 declare @Column nvarchar(255)
 
 select
  @TableCatalog = db_name(),
  @TableOwner = isnull(parsename(@TableName, 2/*Имя схемы*/), N'dbo'),
  @TableName = parsename(@TableName, 1/*Имя объекта*/)

 declare columns cursor local read_only for
  select
   replace(
    replace(
     @Template,
     '%c',
     case when isnull(@UseBracketing, 0) <> 0 then quotename(COLUMN_NAME) else COLUMN_NAME end
    ),
    '%t',
    isnull(
     case when isnull(@UseBracketing, 0) <> 0 then quotename(DOMAIN_NAME) else DOMAIN_NAME end,
     case when isnull(@UseBracketing, 0) <> 0 then quotename(DATA_TYPE) else DATA_TYPE end +
      case
       when DATA_TYPE in ('char', 'nchar', 'varchar', 'nvarchar', 'binary', 'varbinary') then
        '(' + case when CHARACTER_MAXIMUM_LENGTH = -1 then 'max' else convert(varchar(10), CHARACTER_MAXIMUM_LENGTH) end + ')'
       when DATA_TYPE in ('numeric', 'decimal') then
        '(' + convert(varchar(10), NUMERIC_PRECISION) + ',' + convert(varchar(10), NUMERIC_SCALE) + ')'
       else ''
      end
    )
   )
  from
   INFORMATION_SCHEMA.COLUMNS
  where
   TABLE_CATALOG = @TableCatalog and
   TABLE_SCHEMA = @TableOwner and
   TABLE_NAME = @TableName
  order by
   ORDINAL_POSITION

 select
  @result = ''

 open columns
 fetch next from columns into @Column
 while @@fetch_status = 0
  begin
   select @result = @result + case when @result = '' then '' else @Delimeter end + @Column
   fetch next from columns into @Column
  end
 close columns
 deallocate columns

 return @result
end
Примеры использования:
select ufn_GetTableColumnList('Table', '%c', ', ', 0)
select ufn_GetTableColumnList('Table', 'declare @%c %t', ', ', 0)

То, что Вы сделали с использованием курсора можно сделать одним запросом.
Что-то вроде этого :
use master 
go

declare @sql varchar(max)
declare @tbl varchar(127)
set @tbl = 'dbo.spt_monitor'
set @sql = ''

select @sql = 
@sql 
+case
	when @sql <> '' then ','
	else ''
  end
+'['+c.[name]+']'
from sys.[columns] c 
where c.[object_id] = object_id(@tbl)

select @sql = 'select '+ @sql + ' from '+@tbl + ' with (nolock)'
select @sql 
Написано за 5 минут, для демонстрации метода. Делать нормальную обёртку для формирования полноценного запроса лень.
7 сен 09, 15:16    [7627156]     Ответить | Цитировать Сообщить модератору
 Re: Любителям писать 'select * ...'  [new]
invm
Member

Откуда: Москва
Сообщений: 9229
Алексей2003
а если пользовательский тип данных?

Все учтено ;)

x-x,
Курсор был сделан для того, чтобы обойтись только документированными способами.
7 сен 09, 15:19    [7627206]     Ответить | Цитировать Сообщить модератору
 Re: Любителям писать 'select * ...'  [new]
Glory
Member

Откуда:
Сообщений: 104760
invm
Алексей2003
а если пользовательский тип данных?

Все учтено ;)

x-x,
Курсор был сделан для того, чтобы обойтись только документированными способами.

Но так и непонятно, причем тут любители писать 'select * ...'
7 сен 09, 15:22    [7627229]     Ответить | Цитировать Сообщить модератору
 Re: Любителям писать 'select * ...'  [new]
x-x
Member

Откуда:
Сообщений: 230
invm
Все учтено ;)

Как именно?

invm
Курсор был сделан для того, чтобы обойтись только документированными способами.
Ничего недокументированного в этом методе нет.
И область применения Вашей функции лично мне непонятна. Для чего Вы предлагаете её использовать?
7 сен 09, 15:28    [7627285]     Ответить | Цитировать Сообщить модератору
 Re: Любителям писать 'select * ...'  [new]
Senya_L
Member

Откуда: Москва
Сообщений: 5381
invm,

Redgate SQL Prompt все делает в одно нажатие клавиши Tab.
7 сен 09, 15:31    [7627325]     Ответить | Цитировать Сообщить модератору
 Re: Любителям писать 'select * ...'  [new]
invm
Member

Откуда: Москва
Сообщений: 9229
Господа, если у Вас никогда не возникало задач типа:

  • задекларировать N-ое количество переменных, соответствующих по наименованию и типу столбцам некой таблицы;
  • курсором пробежаться по некому набору и для каждой строки вызвать ХП с параметрами, соответсвующими значениям колонок в этой строке;
  • написать update вида:
    update SomeTable set Field1 = @Field1, Field2 = @Field2...

    то данная функция Вам ни к чему.

    Что касается UDT. Если у Вас есть сомнения, что UDT Обрабатываются некорректно, то приводите примеры. Ибо с UDT работаю постоянно и проблем не замечал.

    А RedGate и иже с ним все-таки небесплатны...
  • 7 сен 09, 15:49    [7627468]     Ответить | Цитировать Сообщить модератору
     Re: Любителям писать 'select * ...'  [new]
    locky
    Member

    Откуда: Харьков, Украина
    Сообщений: 62034
    x-x
    То, что Вы сделали с использованием курсора можно сделать одним запросом.

    Можно. но - не нужно.
    ибо, первое - не гарантирует порядок конкатенации строк.
    и, второе - не гарантирует, что конкатенация вообще произойдёт.
    7 сен 09, 15:53    [7627498]     Ответить | Цитировать Сообщить модератору
     Re: Любителям писать 'select * ...'  [new]
    locky
    Member

    Откуда: Харьков, Украина
    Сообщений: 62034
    invm,

    я бы сказал, что наибольший интерес эта функция вызвала бы в качестве плагина для SSMS.
    7 сен 09, 15:54    [7627508]     Ответить | Цитировать Сообщить модератору
     Re: Любителям писать 'select * ...'  [new]
    x-x
    Member

    Откуда:
    Сообщений: 230
    locky
    x-x
    То, что Вы сделали с использованием курсора можно сделать одним запросом.

    Можно. но - не нужно.
    ибо, первое - не гарантирует порядок конкатенации строк.
    и, второе - не гарантирует, что конкатенация вообще произойдёт.

    locky - поясните пожалуйста!
    Первое - с чего вдруг? В скрипте добавьте order by c.[name] будет Вам порядок или Вы о чем?
    Второе - тоже не понял, почему? Если исключить ошибки в стиле неучёта null или превышения длины строки то с чего вдруг?
    7 сен 09, 16:00    [7627563]     Ответить | Цитировать Сообщить модератору
     Re: Любителям писать 'select * ...'  [new]
    не locky
    Guest
    x-x,

    странный глюк
    7 сен 09, 16:03    [7627591]     Ответить | Цитировать Сообщить модератору
     Re: Любителям писать 'select * ...'  [new]
    daw
    Member

    Откуда: Муром -> Москва
    Сообщений: 7381

    > Первое - с чего вдруг? В скрипте
    > <https://www.sql.ru/forum/actualpost.aspx?bid=1&tid=693456&mid=7627498&p=1&act=quot#7627156>
    > добавьте order by c.[name] будет Вам порядок или Вы о чем?
    > Второе - тоже не понял, почему? Если исключить ошибки в стиле неучёта
    > null или превышения длины строки то с чего вдруг?

    здесь:
    http://support.microsoft.com/default.aspx?scid=287515

    только не надо оправдываться, что у вас де выражений в order by не будет.
    там только один из случаев некорректного поведения описан - на этом форуме
    и другие подобные приводились.
    общий вывод - результат такого запроса зависит от плана выполнения.
    повезет с планом - будет щастье. не повезет - ну, не судьба - никто ничего
    и не обещал.

    Posted via ActualForum NNTP Server 1.4

    7 сен 09, 16:06    [7627613]     Ответить | Цитировать Сообщить модератору
     Re: Любителям писать 'select * ...'  [new]
    invm
    Member

    Откуда: Москва
    Сообщений: 9229
    locky
    я бы сказал, что наибольший интерес эта функция вызвала бы в качестве плагина для SSMS.

    При наличии свободного времени, я рассмотрю такую возможность.
    7 сен 09, 16:06    [7627616]     Ответить | Цитировать Сообщить модератору
     Re: Любителям писать 'select * ...'  [new]
    не locky
    Guest
    daw

    ... только не надо оправдываться, что у вас де выражений в order by не будет...


    чуть выше ссылка, где проблема наблюдалась при order by по полю.

    ну а к "лотереи с планом запроса" +1
    7 сен 09, 16:09    [7627644]     Ответить | Цитировать Сообщить модератору
     Re: Любителям писать 'select * ...'  [new]
    x-x
    Member

    Откуда:
    Сообщений: 230
    не locky, daw
    Вот спасибо!
    7 сен 09, 16:11    [7627659]     Ответить | Цитировать Сообщить модератору
     Re: Любителям писать 'select * ...'  [new]
    iap
    Member

    Откуда: Москва
    Сообщений: 46975
    Только непонятно, какую роль играет порядок перечисления полей в данной теме.
    IMHO, там ORDER BY совсем и не нужен!
    7 сен 09, 16:23    [7627757]     Ответить | Цитировать Сообщить модератору
     Re: Любителям писать 'select * ...'  [new]
    invm
    Member

    Откуда: Москва
    Сообщений: 9229
    iap
    Только непонятно, какую роль играет порядок перечисления полей в данной теме.
    IMHO, там ORDER BY совсем и не нужен!
    В общем-то да, для функционала не нужен. А для красоты сгодится :)
    7 сен 09, 16:33    [7627848]     Ответить | Цитировать Сообщить модератору
     Re: Любителям писать 'select * ...'  [new]
    locky
    Member

    Откуда: Харьков, Украина
    Сообщений: 62034
    iap
    Только непонятно, какую роль играет порядок перечисления полей в данной теме.
    IMHO, там ORDER BY совсем и не нужен!

    Ой, нужен.... Очень заморочливо копаться в списке 100+ полей
    7 сен 09, 16:40    [7627906]     Ответить | Цитировать Сообщить модератору
     Re: Любителям писать 'select * ...'  [new]
    i2akai1
    Member

    Откуда: Петербург
    Сообщений: 217
    Прикольно, жалко только что для объявления переменных она мне не подойдет, так как
    куча полей с пробелами в названии :)
    7 сен 09, 16:57    [7628049]     Ответить | Цитировать Сообщить модератору
     Re: Любителям писать 'select * ...'  [new]
    invm
    Member

    Откуда: Москва
    Сообщений: 9229
    i2akai1
    Прикольно, жалко только что для объявления переменных она мне не подойдет, так как
    куча полей с пробелами в названии :)
    Для таких случаев специально предусмотрен параметр @UseBracketing
    7 сен 09, 17:50    [7628448]     Ответить | Цитировать Сообщить модератору
     Re: Любителям писать 'select * ...'  [new]
    locky
    Member

    Откуда: Харьков, Украина
    Сообщений: 62034
    invm
    i2akai1
    Прикольно, жалко только что для объявления переменных она мне не подойдет, так как
    куча полей с пробелами в названии :)
    Для таких случаев специально предусмотрен параметр @UseBracketing


    declare @[Customer No_] varchar(10)
    ?
    7 сен 09, 18:35    [7628638]     Ответить | Цитировать Сообщить модератору
     Re: Любителям писать 'select * ...'  [new]
    i2akai1
    Member

    Откуда: Петербург
    Сообщений: 217
    locky, invm

    declare @[a b] varchar(10)
    Msg 170, Level 15, State 1, Line 1
    Line 1: Incorrect syntax near 'varchar'.
    7 сен 09, 18:41    [7628658]     Ответить | Цитировать Сообщить модератору
     Re: Любителям писать 'select * ...'  [new]
    locky
    Member

    Откуда: Харьков, Украина
    Сообщений: 62034
    i2akai1
    locky, invm

    declare @[a b] varchar(10)
    Msg 170, Level 15, State 1, Line 1
    Line 1: Incorrect syntax near 'varchar'.

    Я знаю, спасибо :)
    7 сен 09, 18:44    [7628668]     Ответить | Цитировать Сообщить модератору
    Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
    Все форумы / Microsoft SQL Server Ответить