|
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. Почему после изменения представления, не все данные отображаются (не все колонки). Как обновить представление
14. Почему после изменения представления, не все данные отображаются (не все колонки). Как обновить представление
15. Получение справочника в виде дерева с уровнями
16. Вставить строку с проверкой на существоание
17. Если вы не нашли желаемого ответа
1.Системные 1.1. Как выполнить команду системы 1.2. Как выполнить код со строки (по типу шаблон() в 1С) 2. Работа с метаданными 2.1. База данных 2.1.1.Имя текущей базы 2.1.2. Список баз на текущем сервере Код: select name from master..sysdatabases | 2.1.3. Проверить есть ли база на сервере Код: IF exists (SELECT 'true' FROM master.dbo.sysdatabases where name = 'mybase') print 'такая база есть' | if db_id('mybase') is null print 'такой базы нет' | 2.2. Параметры пользователя (имя, домен, имя компьютера) 2.2.1. Параметры подлючения пользователя Имя пользователя 2.2.2. Имя SQL - сервера 2.2.3. Имя текущей базы 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)
| | 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.Как посмотреть код хранимой процедуры 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. Создание процедуры с проверкой на существавание IF EXISTS (SELECT name FROM sysobjects WHERE name = N'MyProc') DROP Procedure MyProc GO Create procedure MyProc | if object_id(database.dbo.object) is null EXEC ('create proc t1 AS PRINT 1') GO ALTER proc t1 AS ... | 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. Постоянной таблицы | IF EXISTS (SELECT name FROM dbo.sysobjects where name = 'mytable' and xtype = 'U ')
DROP TABLE mytable | | 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 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. Получение даты начала и конца периодов | 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) | | 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. Управление свойствами на уровне соединения, хранимой процедуры, пакета комманд, тригера и функции Для более подробной информации смотрите: Электронная документация по SQL Server 2005 SET (Transact-SQL)
Инструкции даты и времени (Date and time statements)
SET DATEFIRST - Устанавливает первый день недели в виде числа от 1 до 7 (7 - Восскресенье, текущее состояние - @@DATEFIRST) SET DATEFORMAT - Задает порядок составляющих даты (месяц/день/год) для ввода данных типа datetime или smalldatetime (допустиме значение: mdy, dmy, ymd, ydm, myd, dym. Для изменения языка надо использовать SET LANGUAGE)
Инструкции блокировки (Locking statements)
SET DEADLOCK_PRIORITY - Определяет относительную важность продолжения обработки текущего сеанса, если произошла взаимоблокировка с другим сеансом. SET LOCK_TIMEOUT - Указывает количество миллисекунд, в течение которых инструкция ожидает снятия блокировки. Прочие инструкции (Miscellaneous statements) SET CONCAT_NULL_YIELDS_NULL - Управляет представлением результатов сцепления в виде значений NULL или пустых строковых значений. SET CURSOR_CLOSE_ON_COMMIT - Управляет поведением инструкции Transact-SQL COMMIT TRANSACTION. Значение этого параметра по умолчанию равно OFF. Это означает, что сервер не закроет курсоры при подтверждении транзакции. SET FIPS_FLAGGER - Указывает режим проверки на соответствие стандарту FIPS 127-2. Этот стандарт основан на стандарте SQL-92. SET IDENTITY_INSERT - Позволяет вставлять явные значения в столбец идентификаторов таблицы. SET LANGUAGE - Устанавливает языковое окружение сеанса. Язык сеанса определяет форматы datetime и системных сообщений. SET OFFSETS - Возвращает смещение (позицию относительно начала инструкции) заданного ключевого слова в инструкциях Transact-SQL для приложений DB-Library. SET QUOTED_IDENTIFIER - Заставляет SQL Server следовать правилам SQL-92 относительно кавычек, разделяющих идентификаторы и строки-литералы. Идентификаторы, заключенные в двойные кавычки, могут быть либо зарезервированными ключевыми словами Transact-SQL, либо могут содержать символы, которые обычно запрещены правилами синтаксиса для идентификаторов Transact-SQL.
Инструкции выполнения запросов (Query Execution Statements)
SET ARITHABORT - SET ARITHIGNORE - SET FMTONLY - SET NOCOUNT - Запрещает вывод количества строк, на которые влияет инструкция Transact-SQL или хранимая процедура, в составе результирующего набора. SET NOEXEC - Компилирует каждый запрос, но не выполняет его. SET NUMERIC_ROUNDABORT - SET PARSEONLY - SET QUERY_GOVERNOR_COST_LIMIT - SET ROWCOUNT - Приводит к завершению обработки запроса SQL Server после возвращения указанного количества строк. SET TEXTSIZE -
Инструкции настроек стандарта SQL-92 (SQL-92 Settings statements)
SET ANSI_DEFAULTS - SET ANSI_NULL_DFLT_OFF - SET ANSI_NULL_DFLT_ON - SET ANSI_NULLS - SET ANSI_PADDING - Контролирует способ хранения в столбце значений короче, чем определенный размер столбца, и способ хранения в столбце значений, имеющих замыкающие пробелы, в данных char, varchar, binary и varbinary. 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 - Пример: Установить отображение даты не в ISO формате SET DATEFORMAT mdy 12. Удаление всех данных с таблиц текущей базы данных Будте очень внимательны и перед выполнением ниже приведенного кода сначала сделайте бэкап базы USE MyBase 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) 14. Почему после изменения представления, не все данные отображаются (не все колонки). Как обновить представление. Если в представлении используется выборка данных типа SELECT * FROM, то такое представление достаточно обновить командой "EXEC sp_refreshview 'MyViews'". 15. Получение справочника в виде дерева с уровнями. Данный запрос будет работать только в MS SQL 2005 и выше. Выборка происходит с справочника с таблицей "SC148" WITH Spr(PARENTID, ID, ISFOLDER, SprLevel) AS ( SELECT PARENTID, ID, ISFOLDER, 0 AS SprLevel FROM SC148 WHERE PARENTID = ' 0 ' UNION ALL SELECT e.PARENTID, e.ID, e.ISFOLDER, SprLevel + 1 FROM SC148 e INNER JOIN Spr d ON e.PARENTID = d.ID ) SELECT PARENTID, ID, ISFOLDER, SprLevel FROM Spr 16. Вставить строку с проверкой на существоание UPDATE ... table1 where ... if @@rowcount = 0 insert into table1 или if exists(select top 1 field1 from tabl1 where) UPDATE ... table1 where ... else insert into table1 17. Если вы не нашли |
