FAQ по программированию на Transact SQL (MS SQL Server 2000, 2005) под 1С

1. Системные
1.1. Как выполнить команду системы
1.2. Как выполнить код со строки (по типу шаблон() в 1С)
2. Работа с метаданными
2.1. База данных
2.1.1. Имя текущей базы
2.1.2. Список баз на текущем сервере
2.1.3. Проверить есть ли база на сервере
2.2. Параметры пользователя (имя, домен, имя компьютера)
2.2.1. Параметры подлючения пользователя
2.2.2. Имя SQL - сервера
2.2.3. Имя текущей базы
2.3 Таблицы
2.3.1. Список таблиц в базе
2.3.2 Список столбцов в таблицах базы данных
2.3.3. Описание столбца таблицы
2.3.4. Список полей таблиц с описанием полей
3. Хранимые процедуры
3.1. Список хранимых процедур в базе
3.2.Как посмотреть код хранимой процедуры
3.3.Как изменить код хранимой процедуры
3.4. Существует ли процедура с указанным именем в базе
3.5. Создание процедуры с проверкой на существавание
4. Проверка существования объектов
4.1. Функции
4.2. Процедуры
4.3. Базы данных
4.4. Таблицы
4.4.1. Постоянной таблицы
4.4.2. Временной таблицы
5. Доступ посредством ODBC и OLE DB
5.1. Какой драйвер использовать для работы с DBF файлами языком Transact-SQL
5.2. Пример работы с ADO
6. Работа с датами
6.1. Как получить в запросе список дат за год
6.2. Как заполнить таблицу датами за период (несколько лет)
6.3. Получение дат за определенный преиод
6.3.1. Получение таблицы дат с колонкой типа "Дата"
6.3.2. Получение таблицы дат с колонкой типа "Строка"
6.3.3 Получение дат с использованием функции
6.3.4 Один из самых оптимальных по скорости выполнения и количестве чтений метод получения дат
7. Как удалить дубли с таблицы
8. Как 1С проверяет есть ли запущенная 1С (для верификации)
9. Как преобразовать строку с разделителями в таблицу
10. Перевод чисел в разные системы исчеслений
11. Управление свойствами
11.1. Упраавление свойствами на уровне сервера
11.2. Управление свойтсвами на уровне базы данных
11.3. Управление свойствами на уровне соединения, хранимой процедуры, пакета комманд, тригера и функции
12. Удаление всех данных с таблиц текущей базы данных
13. Если вы не нашли желаемого ответа

1. Системные

1.1. Как выполнить команду системы

xp_cmdshell 'dir c:\'

1.2. Как выполнить код со строки (по типу шаблон() в 1С)

Execute (' command')
или сокращенная форма
EXEC (' command')

2. Работа с метаданными

2.1. База данных

2.1.1.Имя текущей базы

select db_name()

2.1.2. Список баз на текущем сервере Код:

select name from master..sysdatabases

2.1.3. Проверить есть ли база на сервере Код:

IF exists (SELECT  'true' FROM master.dbo.sysdatabases where name = 'mybase')
print 'такая база есть'
вариант 2:
if db_id('mybase') is null
print 'такой базы нет'

2.2. Параметры пользователя (имя, домен, имя компьютера)

2.2.1. Параметры подлючения пользователя

EXEC sp_who @@SPID

Имя пользователя

SELECT SUSER_SNAME()

2.2.2. Имя SQL - сервера

select @@SERVERNAME

2.2.3. Имя текущей базы

select db_name()

2.3 Таблицы

2.3.1. Список таблиц в базе

SELECT * FROM dbo.sysobjects where xtype = 'U '
или же:
SELECT * FROM INFORMATION_SCHEMA.tables

2.3.2 Список столбцов в таблицах базы данных

SELECT * FROM INFORMATION_SCHEMA.COLUMNS

2.3.3. Описание столбца таблицы

declare @tabid int,
@typestr varchar(30)
SET @tabid = OBJECT_ID('_1SACCS')
exec sp_gettypestring @tabid,1,@typestr output
SELECT @typestr

2.3.4. Список полей таблиц с описанием полей

SELECT
    So.name AS TableName,
    SC.name AS ColumnName,
    St.name AS Type,
    SC.length As Length,
    Sc.xprec As [Precision],
    Sc.xscale AS Scale
FROM
    dbo.syscolumns As Sc
    INNER JOIN dbo.sysobjects AS So ON (Sc.ID = So.ID)
    INNER JOIN dbo.systypes AS St ON (Sc.xtype = St.xtype)
WHERE
    (So.xtype IN ('U', 'V'))
    AND (So.name = 'myTable')
ORDER BY
    TableName,
    SC.colid

или воспользоваться хранимой процедурой

exec sp_columns 'myTable''dbo'

или же вот так к примеру:
declare @tName varchar(50)
set @tName = 'myTable'
SELECT @tName AS table_name,
c.[name] AS column_name,
ISNULL(CAST(p.value AS VARCHAR(2000)),'') AS column_description,
COLUMNPROPERTY(OBJECT_ID(@tName),c.[name],'Precision') AS precision_,
ISNULL(COLUMNPROPERTY(OBJECT_ID(@tName),c.[name],'Scale'),0) AS scale,
COLUMNPROPERTY(OBJECT_ID(@tName),c.[name],'AllowsNull') AS AllowsNull,
COLUMNPROPERTY(OBJECT_ID(@tName),c.[name],'IsIdentity') AS IsIdentity,
COLUMNPROPERTY(OBJECT_ID(@tName),c.[name],'IsRowGuidCol') AS IsRowGuidCol,
COLUMNPROPERTY(OBJECT_ID(@tName),c.[name],'IsFulltextIndexed') AS IsFulltextIndexed
FROM dbo.syscolumns c
LEFT JOIN dbo.sysproperties p ON c.[id]=p.[id] AND c.colid=p.smallid
WHERE (p.type=4 OR p.type IS NULL) AND c.[id]=OBJECT_ID(@tName)

Пример получения описаний (сообщил superbluesman: http://sql.ru/forum/actualthread.aspx?bid=1&tid=261738&hl=)

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'ft_GetColumnMetaData' AND type = 'TF')
DROP FUNCTION ft_GetColumnMetaData
GO


-- Возвращает метаданные полей для указанных/ой таблиц/ы
CREATE FUNCTION dbo.ft_GetColumnMetaData
(
@tableName VARCHAR(200), -- имя таблиц/ы (если не указано, то все таблицы)
@WhatSeekBy INT -- 1= отбор таблиц по неточному совпадению наименования (LIKE @tableName+'%')
-- 2= отбор таблиц по наименованию по вхождению (LIKE '%'+@tableName+'%')
-- иначе= отбор таблиц по точному совпадению наименования
)
RETURNS @returnTbl TABLE (table_schema VARCHAR(150),
table_name VARCHAR(200), -- имя таблицы
column_name VARCHAR(200), -- имя поля
data_type VARCHAR(30), -- тип данных поля
precision_ INT, -- общая длина поля
scale INT, -- число знаков после запятой
AllowsNull BIT, -- 1=допустимость NULL-значений
IsIdentity BIT, -- 1=это Identity-поле
column_default VARCHAR(4000), -- значение по умолчанию
column_description VARCHAR(2000), -- описание поля
IsRowGuidCol BIT, -- 1=это RowGuid-поле
IsFulltextIndexed BIT, -- 1=это RowGuid-поле
primary_key_constraint_name VARCHAR(200), -- наименование Primary-ограничения
foreign_key_constraint_name VARCHAR(200), -- наименование Foreign-ограничения
pk_table VARCHAR(200), -- имя внешней таблицы
pk_column VARCHAR(200), -- имя внешнего поля внешней таблицы
update_rule VARCHAR(50), -- действие при обновлении записей
delete_rule VARCHAR(50), -- действие при удалении записей
check_constraint_name VARCHAR(200), -- наименование Check-ограничения
ordinal_position INT) -- порядковый номер поля при его создании в таблице
AS
BEGIN
DECLARE @tName VARCHAR(200)
DECLARE @tbls TABLE (table_name VARCHAR(200))
DECLARE @colDesriptionTbl TABLE (table_name VARCHAR(200),
column_name VARCHAR(200),
column_description VARCHAR(2000),
precision_ INT,
scale INT,
AllowsNull BIT,
IsIdentity BIT,
IsRowGuidCol BIT,
IsFulltextIndexed BIT)

-- Выбираем основную информацию о полях искомых таблиц:
IF ISNULL(@tableName,'')=''
BEGIN
INSERT @tbls (table_name) SELECT table_name FROM INFORMATION_SCHEMA.TABLES
INSERT @returnTbl (table_schema, table_name, column_name, data_type, column_default, ordinal_position)
SELECT CAST(table_schema AS VARCHAR(200)) AS table_schema,
CAST(table_name AS VARCHAR(200)) AS table_name,
CAST(column_name AS VARCHAR(200)) AS column_name,
CAST(data_type AS VARCHAR(30)) AS data_type,
column_default,
ordinal_position
FROM INFORMATION_SCHEMA.COLUMNS
END
ELSE
IF @WhatSeekBy=1
BEGIN
INSERT @tbls (table_name) SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_name LIKE @tableName+'%'
INSERT @returnTbl (table_schema, table_name, column_name, data_type, column_default, ordinal_position)
SELECT CAST(table_schema AS VARCHAR(200)) AS table_schema,
CAST(table_name AS VARCHAR(200)) AS table_name,
CAST(column_name AS VARCHAR(200)) AS column_name,
CAST(data_type AS VARCHAR(30)) AS data_type,
column_default,
ordinal_position
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name LIKE @tableName+'%'
END
ELSE
IF @WhatSeekBy=2
BEGIN
INSERT @tbls (table_name) SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_name LIKE '%'+@tableName+'%'
INSERT @returnTbl (table_schema, table_name, column_name, data_type, column_default, ordinal_position)
SELECT CAST(table_schema AS VARCHAR(200)) AS table_schema,
CAST(table_name AS VARCHAR(200)) AS table_name,
CAST(column_name AS VARCHAR(200)) AS column_name,
CAST(data_type AS VARCHAR(30)) AS data_type,
column_default,
ordinal_position
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name LIKE '%'+@tableName+'%'
END
ELSE
BEGIN
INSERT @tbls (table_name) SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_name=@tableName
INSERT @returnTbl (table_schema, table_name, column_name, data_type, column_default, ordinal_position)
SELECT CAST(table_schema AS VARCHAR(200)) AS table_schema,
CAST(table_name AS VARCHAR(200)) AS table_name,
CAST(column_name AS VARCHAR(200)) AS column_name,
CAST(data_type AS VARCHAR(30)) AS data_type,
column_default,
ordinal_position
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name=@tableName
END

IF @@ROWCOUNT>0
BEGIN
DECLARE cur_tbls CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR SELECT table_name FROM @tbls
OPEN cur_tbls
FETCH NEXT FROM cur_tbls INTO @tName
WHILE (@@FETCH_STATUS=0)
BEGIN
-- Выбираем информацию об описаниях полей и других свойствах полей:
INSERT @colDesriptionTbl (table_name, column_name, column_description,
precision_, scale, AllowsNull, IsIdentity, IsRowGuidCol, IsFulltextIndexed)
SELECT @tName AS table_name,
c.[name] AS column_name,
ISNULL(CAST(p.value AS VARCHAR(2000)),'') AS column_description,
COLUMNPROPERTY(OBJECT_ID(@tName),c.[name],'Precision') AS precision_,
ISNULL(COLUMNPROPERTY(OBJECT_ID(@tName),c.[name],'Scale'),0) AS scale,
COLUMNPROPERTY(OBJECT_ID(@tName),c.[name],'AllowsNull') AS AllowsNull,
COLUMNPROPERTY(OBJECT_ID(@tName),c.[name],'IsIdentity') AS IsIdentity,
COLUMNPROPERTY(OBJECT_ID(@tName),c.[name],'IsRowGuidCol') AS IsRowGuidCol,
COLUMNPROPERTY(OBJECT_ID(@tName),c.[name],'IsFulltextIndexed') AS IsFulltextIndexed
FROM dbo.syscolumns c
LEFT JOIN dbo.sysproperties p ON c.[id]=p.[id] AND c.colid=p.smallid
WHERE (p.type=4 OR p.type IS NULL) AND c.[id]=OBJECT_ID(@tName)

FETCH NEXT FROM cur_tbls INTO @tName
END
CLOSE cur_tbls
DEALLOCATE cur_tbls


-- Выбираем информацию о первичных ключах:
DECLARE @pk TABLE (table_name VARCHAR(200), column_name VARCHAR(200), primary_key_constraint_name VARCHAR(200))
INSERT INTO @pk
SELECT a.table_name, b.column_name,
a.constraint_name AS primary_key_constraint_name
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS a
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE b on b.constraint_name=a.constraint_name
WHERE a.constraint_type='PRIMARY KEY'

-- Выбираем информацию о внешних ключах:
DECLARE @fk TABLE (table_name VARCHAR(200), column_name VARCHAR(200), foreign_key_constraint_name VARCHAR(200),
pk_table VARCHAR(200), pk_column VARCHAR(200), update_rule VARCHAR(50), delete_rule VARCHAR(50))
INSERT INTO @fk
SELECT a.table_name, b.column_name,
a.constraint_name AS foreign_key_constraint_name,
c.pk_table, c.pk_column, c.update_rule, c.delete_rule
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS a
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE b on b.constraint_name=a.constraint_name
INNER JOIN (SELECT u.constraint_name, k.table_name AS pk_table, k.column_name AS pk_column,
r.update_rule, r.delete_rule
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE u
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS r ON r.constraint_name=u.constraint_name
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE k ON r.unique_constraint_name=k.constraint_name) c
ON a.constraint_name=c.constraint_name
WHERE a.constraint_type='FOREIGN KEY'

-- Выбираем информацию об других ограничениях, накладываемых на столбцы:
DECLARE @chk TABLE (table_name VARCHAR(200), column_name VARCHAR(200), check_constraint_name VARCHAR(200))
INSERT INTO @chk
SELECT a.table_name, b.column_name,
a.constraint_name AS check_constraint_name
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS a
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE b on b.constraint_name=a.constraint_name
WHERE a.constraint_type='CHECK'
--------------------------------------------------------------------------------------------------------------------



UPDATE @returnTbl SET column_default=ISNULL(c.column_default,''),
precision_=s.precision_,
scale=s.scale,
AllowsNull=s.AllowsNull,
IsIdentity=s.IsIdentity,
IsRowGuidCol=s.IsRowGuidCol,
IsFulltextIndexed=s.IsFulltextIndexed,
column_description=s.column_description,
primary_key_constraint_name=ISNULL(p.primary_key_constraint_name,''),
foreign_key_constraint_name=ISNULL(f.foreign_key_constraint_name,''),
pk_table=ISNULL(f.pk_table,''),
pk_column=ISNULL(f.pk_column,''),
update_rule=ISNULL(f.update_rule,''),
delete_rule=ISNULL(f.delete_rule,''),
check_constraint_name=ISNULL(h.check_constraint_name,'')
FROM @returnTbl c
INNER JOIN @colDesriptionTbl s ON c.table_name=s.table_name AND c.column_name=s.column_name
LEFT JOIN @pk p ON c.table_name=p.table_name AND c.column_name=p.column_name
LEFT JOIN @fk f ON c.table_name=f.table_name AND c.column_name=f.column_name
LEFT JOIN @chk h ON c.table_name=h.table_name AND c.column_name=h.column_name
END

RETURN
END

-- Примеры вызова:
/*
SELECT * FROM dbo.ft_GetColumnMetaData(NULL, NULL) ORDER BY table_name, column_name
SELECT * FROM dbo.ft_GetColumnMetaData('PS_company', 0) ORDER BY table_name, column_name
SELECT * FROM dbo.ft_GetColumnMetaData('PS', 1) ORDER BY table_name, column_name
SELECT * FROM dbo.ft_GetColumnMetaData('currency', 2) ORDER BY table_name, column_name
*/

 

3. Хранимые процедуры

Вызов хранимой процедуры выполняется командой EXEC тогда, когда в пакете содержится более одной команды.

3.1. Список хранимых процедур в базе

SELECT * FROM dbo.sysobjects WHERE xtype = 'P '

3.2.Как посмотреть код хранимой процедуры

sp_helptext 'MyProc'

3.3.Как изменить код хранимой процедуры

alter procedure _1sp__1SJOURN_TLockX AS
set nocount on
declare
@i integer
select @i=1
from
_1SJOURN(ROWLOCK HOLDLOCK XLOCK)
where 0=1 

3.4. Существует ли процедура с указанным именем в базе


SELECT name FROM sysobjects WHERE name = N'MyProc' and xtype = 'P '

3.5. Создание процедуры с проверкой на существавание

Вариант1:
IF EXISTS (SELECT name FROM sysobjects WHERE name = N'MyProc')
    DROP Procedure MyProc
GO
Create procedure MyProc

Вариант2:
if object_id(database.dbo.object) is null
   EXEC ('create proc t1 AS PRINT 1')
GO
ALTER proc t1 AS
...
Вариант3:
if object_id('OwnerName.ProcName', 'P') is null
  EXEC ('create proc OwnerName.ProcName AS RETURN')
GO
ALTER OwnerName.ProcName
...

4. Проверка существования объектов

4.1. Функции


IF EXISTS (SELECT name FROM sysobjects WHERE name = 'my_fumc' AND type = 'TF')
DROP FUNCTION my_fumc

4.2. Процедуры


IF EXISTS (SELECT name FROM sysobjects WHERE name = 'MyProc' and xtype = 'P ')
DROP PROC MyProc



4.3. Базы данных


if db_id('mybase') is null create database mybase ...

4.4. Таблицы

4.4.1. Постоянной таблицы

Вариант 1:
IF EXISTS (SELECT name FROM dbo.sysobjects where name = 'mytable' and xtype = 'U ')
DROP
TABLE mytable

Вариант 2:
IF EXISTS (SELECT 'True' FROM sysobjects WHERE id = object_id(N'MyTable') AND SYSSTAT & 0xf = 3)
DROP
TABLE mytable

4.4.2. Временной таблицы

IF EXISTS (SELECT * from tempdb..sysobjects where id = object_id('tempdb..#mytable'))
DROP
TABLE #mytable

5. Доступ посредством ODBC и OLE DB

5.1. Какой драйвер использовать для работы с DBF файлами языком Transact-SQL

ODBC:
http://msdn.microsoft.com/library/en-us/odbc/htm/vfpodbcvisualfoxproodbcdriveroverview.asp
по языку: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetmicrosoft_excel_driver_programming_considerations.asp
OLE DB:
Найти ссылку можно здесь: http://msdn.microsoft.com/vfoxpro
или же: http://www.microsoft.com/downloads/details.aspx?FamilyId=E1A87D8F-2D58-491F-A0FA-95A3289C5FD4&displaylang=en

5.2. Пример работы с ADO

CN = СоздатьОбъект("ADODB.Connection");
  CN.ConnectionString = "Driver={SQL Server};Server=....;UID=....;pwd=....;Database=....";
  CN.ConnectionTimeOut = 15;
  CN.CursorLocation = 3;
  Попытка
    CN.Open();
   
    Запрос ="SELECT ...";
    РезультатЗапроса = CN.Execute(Запрос);
    Пока РезультатЗапроса.EOF() = 0 Цикл
      НомерДокумента=Строка(РезультатЗапроса.Fields("NumDoc").Value);
      ДатаДокумента=Дата(РезультатЗапроса.Fields("DateIn").Value);
 
     
      РезультатЗапроса.MoveNext();
    КонецЦикла;
  Исключение
  КонецПопытки;
  CN.Close();

6. Работа с датами

6.1. Как получить в запросе список дат за год?

DECLARE @DateStart DateTime
SET @DateStart = Convert(datetime,'20060101',112);
select
top 365 dateadd(dd, -Day(@DateStart),
dateadd(mm,-Month(@DateStart)+1,@DateStart)) +(select count(*) from master..sysobjects where id<=a.id) AS [Date]
from master..sysobjects AS a
Order by [Date]

6.2. Как заполнить таблицу датами за несколько лет

CREATE TABLE #PeriodTMP
(
PeriodID int NOT NULL IDENTITY (1, 1),
PeriodDate datetime NOT NULL
)

DECLARE @DateStart DateTime
DECLARE @DateEND DateTime
SET @DateStart = Convert(datetime,'19960101',112);
SET @DateEND = Convert(datetime,'20101231',112)
While @DateStart <= @DateEND
BEGIN
IF DatePart(yy,@DateStart)%4 = 0
INSERT INTO #PeriodTMP (PeriodDate)
select top 366 dateadd(dd, -Day(@DateStart), dateadd(mm,-Month(@DateStart)+1,@DateStart)) +(select count(*)
from
master..sysobjects where id<=a.id) AS [Date]
from master..sysobjects a Order by [Date]
ELSE
INSERT INTO #PeriodTMP (PeriodDate)
select top 365 dateadd(dd, -Day(@DateStart), dateadd(mm,-Month(@DateStart)+1,@DateStart)) +(select count(*)
from
master..sysobjects where id<=a.id) AS [Date]
from master..sysobjects a Order by [Date]
SELECT @DateStart = dateadd(yy,1,@DateStart)

END

6.3. Получение дат за определенный преиод

6.3.1. Получение таблицы дат с колонкой типа "Дата"

DECLARE @DateStart DateTime
SET @DateStart = Convert(datetime,'20060101',112);
select
top 365 dateadd(dd, -Day(@DateStart),
dateadd(mm,-Month(@DateStart)+1,@DateStart)) +(select count(*) from master..sysobjects where id<=a.id) AS [Date]
from master..sysobjects AS a
Order by [Date]

6.3.2. Получение таблицы дат с колонкой типа "Строка"

declare @start datetime
declare @end datetime
declare @current datetime

set @start = '20060101'
set @end = '20060701'
set @current = @start
create table #days
(
day_id int identity,
dt_stamp datetime
)

while (select @current) <= @end
begin
insert into #days values (@current)
set @current = dateadd(day, 1, @current)
end

select * from #days

drop table #days

6.3.3 Получение дат с использованием функции

CREATE FUNCTION fn_dates(@from AS DATETIME, @to AS DATETIME)
RETURNS @Dates TABLE(dt DATETIME NOT NULL PRIMARY KEY)
AS
BEGIN
DECLARE @rc AS INT
SET @rc = 1
INSERT INTO @Dates VALUES(@from)

WHILE @from + @rc * 2 - 1 <= @to
BEGIN
INSERT INTO @Dates
SELECT dt + @rc FROM @Dates
SET @rc = @rc * 2
END
INSERT INTO @Dates
SELECT dt + @rc FROM @Dates
WHERE dt + @rc <= @to

RETURN
END
GO

SELECT dt FROM fn_dates('20030901', '20040831')

6.3.4 Один из самых оптимальных по скорости выполнения и количестве чтений метод получения дат

select A.date
from (select date = cast((b.number * 0x100) + a.number as datetime)
from master..spt_values a
join
master..spt_values b
on (b.number * 0x100) + a.number <= cast(cast('20101231' as datetime) as integer)
and
(b.number * 0x100) + a.number >= cast(cast('20000101' as datetime) as integer)
where
a.type = 'p'
and
b.type = 'p'
and b.number * 0x100 <= cast(cast('20101231' as datetime) as integer)
) as A
order by A.date

Приведенный код является оптимальным по скорости выполнения и по количеству чтений. Приведенный пример в пунтке 6.3.3 - также является оптимальным по скорости выполнения, но количество чтений данных в нем выше чем в приведенном в этом пунтке.

6.3.5. Получение даты начала и конца периодов

(исходный текст: http://www.sql.ru/faq/faq_topic.aspx?fid=115)
set nocount on
declare @d datetime
set @d=convert(char(8),getdate(),112)
select 'Дата ',@d
select 'первый день месяца',
dateadd(day,1-day(@d),@d)
select 'последний день месяца',
dateadd(month,1,dateadd(day,1-day(@d),@d))-1
select 'первый день года',
dateadd(day,1-datepart(dayofyear,@d),@d),
convert(datetime,'1/1/'+convert(char(4),year(@d)),101)
select 'последний день года',
convert(datetime,'12/31/'+convert(char(4),year(@d)),101)
select 'первый день квартала',
convert(datetime,convert(varchar(2),(month(@d)-1)/3*3+1)+'/1/'+convert(char(4),year(@d)),101),
convert(datetime,convert(varchar(2),convert(varchar(2),(datepart(quarter,@d)-1)*3)+1)+'/1/'+convert(char(4),year(@d)),101)
select 'последний день квартала',
dateadd(month,3,convert(datetime,convert(varchar(2),(month(@d)-1)/3*3+1)+'/1/'+convert(char(4),year(@d)),101))-1

7. Как удалить дубли с таблицы


set nocount on
create table #test(id int, val int)

insert into #test values (1, 1)
insert into #test values (2, 1)
insert into #test values (3, 2)
insert into #test values (4, 3)
insert into #test values (5, 3)
insert into #test values (6, 4)

select * from #test

delete from #test where id not in (select min(id) from #test group by val)
select * from #test

drop table #test
set nocount off





8. Как 1С проверяет есть ли запущенная 1С (для верификации)

Select COUNT(*) from master..sysprocesses where dbid=DB_ID(DB_NAME()) and program_name='1CV7'
            


9. Как преобразовать строку с разделителями в таблицу

declare @inp varchar(1000)
SET @inp = 'Иванов, Петров, Сидоров, Дудкин, Пупкин, Билли, Боря'
declare @s varchar(1000)
set @s='select ltrim('''+ replace(@inp, ',', ''') name union select ltrim(''')+''')'
print(@s)
exec (@s)

Для SQL 2005:

create table #tmp(id int, symb varchar(100))
insert #tmp
select 1, 'a b2 c d qwerty' --разделитель пробел
union all select 2, 'ttt c c c bhyet d'
union all select 3, 'x'
;with q(id, lvl, item, tail) as (
select t.id, 1
,substring(t.s, 2, charindex(' ',t.s,2)-charindex(' ',t.s,1))
,substring(t.s, charindex(' ',t.s,2), len(t.s)-charindex(' ',t.s,1))
from (select id, ' '+ltrim(rtrim(symb))+' ' [s] from #tmp) t
union all
select id, lvl+1
,substring(tail, 2, charindex(' ',tail,2)-charindex(' ',tail,1))
,substring(tail, charindex(' ',tail,2), len(tail)-charindex(' ',tail,1))
from q where charindex(' ',tail,2)-charindex(' ',tail,1)>0
)
select id, item from q order by id,lvl
option(maxrecursion 0)
drop table #tmp


10. Перевод чисел в разные системы исчеслений


-- последовательность чисел от одного до другого
CREATE FUNCTION sequence(
@start int, -- с
@end int) -- по
RETURNS @seq TABLE (i int )
AS
BEGIN
declare @n int
set @n=@start
while 2*2=4
begin
insert @seq select @n
set @n=@n+1
if @n>@end break
end
RETURN
END
go
-- Перевод чисел из одной системы счисления в другую
CREATE FUNCTION ConvSyst(
@num int, -- переводимое число
@syst int) -- разрядность системы, в которую переводится
RETURNS varchar(99)
AS
BEGIN
declare @r varchar(99)
set @r='';
while @num>0
select @r=char(case when @num%@syst < 10
then @num%@syst + ascii('0')
else @num%@syst + ascii('A')-10 end) +@r, @num=@num / @syst
return @r
END
Второй вариант.
declare @S varchar(100)
declare @L int
--set @S = '01'
--set @S = '01234567'
--set @S = '0123456789'
--set @S = '0123456789ABCDEF'
set @S = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ[]{}'
set @L = len( @S) --основание системы исчисления
declare @intid int
declare @strid varchar(128)
set @strid = ''
set @intid = 256
while 0 = 0 begin
set @strid = substring(@S, 1 + @intid % @L ,1) + @strid
set @intid = @intid / @L
if @intid = 0 break
end
select @strid
-- Процедуры по конвертации 10-36, 30-10
-- Проверим есть ли такие про
--IF EXISTS(SELECT name FROM sysobjects WHERE name = 'Convert10To36') DROP Procedure 'Convert10To36'
CREATE PROCEDURE [Convert10To36] @Deci INT, @Res36 CHAR(9) OUTPUT AS
SET NOCOUNT ON
DECLARE @j INT
DECLARE @Arr36 CHAR(36)
SELECT @Arr36 = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'
SELECT @Res36 = ''
SELECT @j = LOG(@Deci)/LOG(36) +1
while @j>0
begin
SELECT @Res36 = LTRIM(RTRIM(@Res36)) + SUBSTRING(@Arr36, @Deci/POWER(36,@j-1) +1 ,1)
SELECT @Deci = @Deci%POWER(36,@j-1)
SELECT @j =@j-1
end
GO
--IF EXISTS(SELECT name FROM sysobjects WHERE name = 'Convert36To10') DROP Procedure 'Convert36To10'
CREATE PROCEDURE [Convert36To10] @Res36 CHAR(9), @Deci INT OUTPUT AS
SET NOCOUNT ON
DECLARE @j INT
DECLARE @Arr36 CHAR(36)
SELECT @Arr36 = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'
SELECT @Deci = 0
SELECT @j = 1
while @j <= LEN(LTRIM(RTRIM(@Res36)))
begin
if @j <> 1
SELECT @Deci = @Deci*36
SELECT @Deci = @Deci + CHARINDEX(SUBSTRING(LTRIM(RTRIM(@Res36)), @j,1),@Arr36) -1
SELECT @j = @j+1
End

11. Управление свойствами

Иерархия значений свойств имеют такую иерархию:
  • Значения на уровне соедниения, хранимой процедуры, пакета комманд, тригера или функции.
  • Значения на уровне базы данных.
  • Значения на уровне сервера.
Т.е. наибольший приоритет имеют значения, установленные на верхнем уровне (хранимой процедуры, пакета комманд, тригера или функции).
Следует отметить, что значения на разных уровенях устанавливаются по разному (разными коммандами).
  • Значения на увровне соединения: SET [@Optvalue =]  'Value'
  • Значения на уровне базы данных: EXEC sp_dboption [@Optvalue =]  'Value'
  • Значения на уровне сервера: EXEC sp_configure [@Optvalue =]  'Value'

11.1. Упраавление свойствами на уровне сервера

Для просмотра списка расширенных параметров необходимо включить опцию просмотра этих параметров:
USE master;
GO
EXEC sp_configure 'show advanced option', '1';
Теперь для применения измененной опции надо выполнить команду RECONFIGURE;
В целом скрипт для просмотра всех возможных опций надо вполнить такой код:

USE master;
GO
EXEC sp_configure 'show advanced option', '1';
RECONFIGURE;
EXEC sp_configure;

Для просмотра возможных значений и списка параетров надо выполнить:
EXEC sp_configure

11.2. Управление свойтсвами на уровне базы данных

EXEC sp_dboption  - показывает списов возможных параметров для текущей базы.
EXEC sp_dboption 'base_' - показывает список измененных (установленных) параметров для указаной базы.
EXEC sp_dboption 'base_', 'auto create statistics' - проверка установлена или нет конкретная опция (в данном случае 'auto create statistics')

11.3. Управление свойствами на уровне соединения, хранимой процедуры, пакета комманд, тригера и функции

Date and time statements
SET DATEFIRST
SET DATEFORMAT

Locking statements
SET DEADLOCK_PRIORITY
SET LOCK_TIMEOUT
 
Miscellaneous statements
SET CONCAT_NULL_YIELDS_NULL
SET CURSOR_CLOSE_ON_COMMIT
SET FIPS_FLAGGER
SET IDENTITY_INSERT
SET LANGUAGE
SET OFFSETS
SET QUOTED_IDENTIFIER

Query Execution Statements
SET ARITHABORT
SET ARITHIGNORE
SET FMTONLY
SET NOCOUNT
SET NOEXEC
SET NUMERIC_ROUNDABORT
SET PARSEONLY
SET QUERY_GOVERNOR_COST_LIMIT
SET ROWCOUNT
SET TEXTSIZE

SQL-92 Settings statements
SET ANSI_DEFAULTS
SET ANSI_NULL_DFLT_OFF
SET ANSI_NULL_DFLT_ON
SET ANSI_NULLS
SET ANSI_PADDING
SET ANSI_WARNINGS
 
Statistics statements
SET FORCEPLAN
SET SHOWPLAN_ALL
SET SHOWPLAN_TEXT
SET SHOWPLAN_XML
SET STATISTICS IO
SET STATISTICS XML
SET STATISTICS PROFILE
SET STATISTICS TIME
 
Transactions statements
SET IMPLICIT_TRANSACTIONS
SET REMOTE_PROC_TRANSACTIONS
SET TRANSACTION ISOLATION LEVEL
SET XACT_ABORT
 

12. Удаление всех данных с таблиц текущей базы данных

  DECLARE @TableName varchar(30) 
  DECLARE myCur CURSOR FOR
Select
    RTRIM(CONVERT(varchar(30),TABLE_NAME))
from
    INFORMATION_SCHEMA.TABLES
WHERE
    TABLE_TYPE='BASE TABLE'
    AND TABLE_NAME<>'dtproperties'
 
  OPEN myCur
  FETCH NEXT FROM myCur INTO @TableName 
  WHILE @@FETCH_STATUS=0 BEGIN 
      EXEC ('DELETE FROM '+@TableName)
      FETCH NEXT FROM myCur INTO @TableName 
  END 
  CLOSE myCur
  DEALLOCATE myCur
13. Как сбросить счетчик в колонке Identyti:

DBCC CHECKIDENT ('tbl_IDLinesDoc', RESEED, 0)

13. Если вы не нашли желаемого ответа

Можете воспользоваться поиском на таких сайтах:
http://sql.ru/
http://thebeautiful.narod.ru/1csql.htm
http://www.sinor.ru/~my1c/knowhow.html
http://www.1csql.ru/materials/faq/admin.html
http://www.softpoint.ru/article.php

Или задать вопрос на одном из форумов:
http://sql.ru/forum/actualtopics.aspx?bid=1
http://1c.realnet.ru/forum/f.php
http://www.kuban.ru/cgi-bin/forum/forum9.cgi
http://itland.ru/forum/index.php
http://www.forum.mista.ru/index.php
http://www.itpb.ru/forum4/

© pvase 2007