Список полей таблиці в MSSQL
Розкажу про 1 трюк для MSSQL який здорово полегшує життя при написанні скриптів. Проблема в тому, що при написанні запитів (зокрема insert`a) часто потрібно вказати таблицю із списком всіх її полів. Якщо полей 5-10, то їх імена можна виписати вручну, але якщо таблички більші, то робити це вручну це вже збочення.
Проблема
Наприклад, є така табличка:
і нам потрібно вставити туди якісь дані за допомогою SQL. Це можна зробити так:
insert into tblGuestBookEntries (gbeDate, gbeName, gbeText, gbeEmail, gbeIcq, gbeIP, gbeBrowser, gbeCommentsCount)
values (getdate(), 'test', 'test', null, null, '127.0.0.1', 'test', 0)
або так:
insert into tblGuestBookEntries (gbeDate, gbeName, gbeText, gbeEmail, gbeIcq, gbeIP, gbeBrowser, gbeCommentsCount)
select getdate(), 'test', 'test', null, null, '127.0.0.1', 'test', 0
union
select getdate(), 'test', 'test', null, null, '127.0.0.1', 'test', 0
Другий варіант трохи зручніший, оскільки можна вставити зразу декілька рядків. Але не про то мова. Як бачимо в обидвох випадках доводиться перелічувати всі поля таблиці. Це доводиться робити, оскільки поле gbeID є автоінкрементним (тобто має identity), і ми не можемо явно вставити туди якесь значення. Зразу спадає на думку вставити туди значення default ось так:
insert into tblGuestBookEntries
values (default, getdate(), 'test', 'test', null, null, '127.0.0.1', 'test', 0)на що SQL Server матюкається так:
Я спочатку дуже нашвидкуруч прочитав цей меседж і подумав, що виставлення IDENTITY_INSERT в ON вирішить дану проблему, внаслідок чого сотворився наступний код:
set identity_insert tblGuestBookEntries on
insert into tblGuestBookEntries
values (isnull(ident_current('tblGuestBookEntries') + 1, 1),
getdate(), 'test', 'test', null, null, '127.0.0.1', 'test', 0)
set identity_insert dbo.tblGuestBookEntries offАле запустивши його я отримав цю ж помилку, після чого таки повністю прочитав фразу ... can only be specified when a column list is used ...
Отже без перерахування полей не обійтись.
Вирішення
Перерахувати поля таблиці можна за допомогою спеціальної інформаційної таблиці:
select column_name + ', '
from information_schema.columns
where table_name = 'tblGuestBookEntries'
або за допомогою сторед процедури:
sp_columns 'tblGuestBookEntries'
Перший варіант зручніший, бо видає тільки назви полей. Я ще для зручності доставляю кому в кінці. Після видачі всіх полей можна їх копіювати в SQL-код, але оскільки кожне наступне поле в новому рядку, то якщо полей штук так з 20, то виходять макарони, тобто дуже довгий код, який важко переглядати. Тому приходиться ще забрати ентери, для цього я закопійовую все в текстовий редактор і замінюю «\\n» на «». Щоб цього постійно не робити було б добре якби була якась агрегатна функція concat, яка б зліплювала усі записи в один рядок. Нажаль такої функції в MSSQL поки немає, тому доводиться збочуватись. Трохи побавившись з курсорами я вродив таку процедуру:
create proc sprColumnsList
(
@tableName varchar(max),
@delimiter varchar(max) = ', '
)
as
begin
declare mycursor cursor for
select column_name
from information_schema.columns
where table_name = @tableName
declare @allColumns varchar(max)
declare @curColumn varchar(max)
open mycursor
fetch next from mycursor
into @allColumns
while 1=1
begin
fetch next from mycursor
into @curColumn
if (@@fetch_status <> 0)
break;
set @allColumns = @allColumns + @delimiter + @curColumn
end
close mycursor
deallocate mycursor
select @allColumns as ColumnsList
end
Тут створюється курсор, який пробігається по всіх ряках з назвами полей таблиці, запихає їх у змінну і виводить її.
Тепер можна отримати список полей таблиці просто виконавши
sprColumnsList 'tblGuestBookEntries'
Приклад 2:
declare @x varchar(max)
set @x = ',' + char(13) + char(9)
exec sprColumnsList 'tblUsers', @x
Тут отримуємо список із комою, переносом рядка і одним табом зліва. (Щоб показувались переноси рядків у MSSQL Management Studio, треба включити текстовий вивід: у Tools > Options > Query Results виставити Results to text)
До речі, з появою MSSQL 2005 появився ще 1 спосіб. Тепер можна писати сторед процедури на .NET мовах (C#, VB.NET, ...). Цього способу я ще не досліджував, але він дуже підходить для цієї проблемки.
Але одне зауваження - останнє поле дублюється в списку.
Треба в циклі ще добавити перевірку
if (@@fetch_status = 0)
set @allColumns = @allColumns + @delimiter + @curColumn