select
/* 参照可能なデータベースサーバー一覧の取得 */
 name
,data_source 
from sys.servers
WHERE 1=1
--and name like @'%此処に対象を指定%'

select
/* データベース一覧を取得 */
 name 
from sys.databases
WHERE 1=1
--and name like @'%此処に対象を指定%'

select 
/* セッション一覧の取得 */
 spid
,hostname
,loginame
,program_name
,login_time
,last_batch
,status
from sys.sysprocesses

select
/* 自身のセッションIDを取得 */
 @@spid

SELECT
/* ストアドプロシージャ一覧取得 */
 cast(sys_o.name as varchar) as name
FROM sysobjects sys_o
where sys_o.xtype = 'P'
--and sys_o.name like @'%此処に対象を指定%'

SELECT
/* ストアードプロシジャー内容の取得 */
'--[[['+cast(sys_o.name as NCHAR(110))+']]]--'+NCHAR(13)+NCHAR(10)+ /* 先頭行にＩＤを付加する場合 */
sys_c.definition as SpStr
FROM sys.sql_modules sys_c ,
sys.objects sys_o
WHERE sys_c.object_id = sys_o.object_id
and type = 'P'
--and sys_o.name like @'%此処に対象を指定%'

SELECT
/* ストアードプロシジャー内容の取得(各行の先頭にＩＤ付加) */
 cast(name+char(9)+replace(SpStr,char(10),char(10)+name+char(9)) as text)/*先頭にプログラムＩＤを付ける時*/
from (
  select
  /* ストアードプロシジャー内容の取得 */
   SpStr
  ,name
  from (
    SELECT
     sys_c.definition as SpStr
    ,sys_o.name
    FROM sys.sql_modules sys_c
    left outer join sys.objects sys_o
    on sys_c.object_id = sys_o.object_id
    where 1 = 1
    and type = 'P'
    --and sys_o.name like @'%此処に対象を指定%'
  ) a
) a

--select
/* ストアードプロシジャーを行単位で取得 */
WITH LineStr(StrName,StrData,StrPos_st,StrPos_en)
as (
  SELECT
   sys_o.name        as StrName
  ,sys_c.definition  as StrData
  ,cast(1 as bigint) as StrPos_st
  ,CHARINDEX(NCHAR(13) + NCHAR(10),sys_c.definition) as StrPos_en
  FROM sys.sql_modules        sys_c
  left outer join sys.objects sys_o
  on sys_o.object_id = sys_c.object_id
  WHERE type = 'P'
  union all
  SELECT
   StrName
  ,StrData
  ,StrPos_en+2 as StrPos_st
  ,CHARINDEX(NCHAR(13)+NCHAR(10),StrData,StrPos_en+2) as StrPos_en
  from LineStr
  where StrPos_en > 0
)
select 
 StrName
,Line
,LineSrc
from (
  select 
   StrName
  ,ROW_NUMBER() OVER (PARTITION BY StrName ORDER BY StrPos_st) as Line
  ,SUBSTRING(StrData,StrPos_st,case when StrPos_en > 0 then StrPos_en else len(StrData)+10 end-StrPos_st) as LineSrc
  from LineStr
) Src
where 1=1
--and  StrName   like @'%此処に対象を指定%'
--and  LineSrc   like @'%検索する文字列を指定%'
order by
 StrName
,Line
option (MAXRECURSION 32767)

select 
/* テーブル一覧取得 */
 cast(a.name as varchar(50)) as テーブル名 
,case  
 when a.xtype = 'S'  then 'SysTable' 
 when a.xtype = 'U'  then 'UserTable' 
 when a.xtype = 'V'  then 'View' 
 end  as TBLタイプ 
from dbo.sysobjects a 
WHERE 1=1
--and a.name like @'%此処に対象を指定%' 
and a.xtype in ('S','U','V')
order by テーブル名

select 
/* テーブル構造取得 */
 cast(a.name as varchar(50)) as テーブル名 
,case  
 when a.xtype = 'S'  then 'SysTable' 
 when a.xtype = 'U'  then 'UserTable' 
 when a.xtype = 'V'  then 'View' 
 end  as TBLタイプ 
,cast(b.colid as int) as フィールド順 
,cast(b.name as varchar(50)) as フィールド名 
,cast(c.name as varchar(20)) as タイプ 
,b.prec  as 桁数 
,b.scale as 少数部 
,d.keyno as キー順 
,case when b.isnullable = 0 then 'not null' end as null値 
from dbo.sysobjects a 
left outer join dbo.syscolumns b 
on a.id = b.id 
left outer join dbo.systypes c 
on b.xtype = c.xtype 
and b.usertype = c.usertype 
left outer join dbo.sysindexkeys d 
on a.id = d.id 
and b.colid =d.colid 
and d.indid = 1 
WHERE 1=1
--and a.name like @'%此処に対象を指定%' 
and a.xtype in ('S','U','V')
order by テーブル名,フィールド順

SELECT
/* ビューの定義を取得 */
 '--[[['+cast(schemas.name + '.' + views.name as CHAR(110))+']]]--'+NCHAR(13)+NCHAR(10)+ /* 先頭行にＩＤを付加する場合 */
+modules.definition                   AS Definition
FROM sys.views views
INNER JOIN sys.objects objects
ON views.object_id = objects.object_id
INNER JOIN sys.schemas schemas
ON objects.schema_id = schemas.schema_id
INNER JOIN sys.sql_modules modules
ON objects.object_id = modules.object_id
where 1 = 1
--and views.name like @'%此処に対象を指定%'
ORDER BY
 schemas.name + '.' + views.name

SELECT
/* 実行済みSQL取得 */
*
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY last_execution_time DESC

SELECT
/* カレントデータベース確認 */
DB_NAME() as カレントデータベース

--select
/* 基本的な再帰 */
with Row(p) AS (
 select 1 as p
 UNION ALL
 select p+1 as p from Row
 where p < 32767
)
select p from Row
option (MAXRECURSION 32767)

--select
/* 値の横縦変換 */
WITH Row(p) AS (
 select 0 as p
 UNION ALL
 select p+1 as p from Row
 where p < 32767
)
,
ItemList as (
 select 
  substring(Item
 ,p+1
 ,charindex(',',substring(Item+',',p+1,LEN(Item)))-1
 ) as v
 from (
--    select @ItemList as Item
   select '010000,020,030,040000,050000' as Item
 ) a
 left outer join Row
 on substring(Item,p,1)=',' or p=0
)
select * from ItemList
option (MAXRECURSION 32767)

--select
/* 値の横縦変換(依存型) */
WITH ListStr(Str) AS (
--    select @Str as Str
 select '010000,020,030,040000,050000' as Str
 UNION ALL
 select 
  substring(Str,charindex(',',Str+',')+1,LEN(Str)) as Str
 from ListStr
 where len(Str) > 1
)
,
VStr as (
 select
  substring(Str,1,charindex(',',Str+',')-1) as Str 
 from ListStr
 where Str <> ''
)
select * from VStr
option (MAXRECURSION 32767)

--select
/* 値の横縦変換(速い？) */
WITH Row(p,Val,Str) AS (
 select
   0 as p
  ,cast('' as Nvarchar(100)) as val
 ,'010000,020,030,040000,050000' as str
 UNION ALL
 select
  p+1 as p 
 ,cast(substring(Str,1,charindex(',',Str+',')-1) as nvarchar(100)) as Val
 ,substring(Str,charindex(',',Str+',')+1,LEN(Str)) as Str
 from Row
 where len(Str)>1
)
select * from Row
where p>0

select 
/* 一番多く使われている値を取得 */
 TableName
,TypeName
from (
  select
   a.name as TableName
  ,c.name as TypeName
  ,ROW_NUMBER() OVER (PARTITION BY a.name ORDER BY count(*) desc) as seq
  from dbo.sysobjects a 
  left outer join dbo.syscolumns b 
  on a.id = b.id 
  left outer join dbo.systypes c 
  on b.xtype = c.xtype 
  and b.usertype = c.usertype 
  WHERE 1=1
  and a.xtype = 'U'
  group by 
   a.name
,c.name
) a
where seq = 1  --ここが味噌

--select
/* 次のレコードを使用する(パフォーマンスは保証できない) */
WITH Data AS (
select 'A' as CD,cast('20000101' as datetime) as Start UNION ALL
select 'A' as CD,cast('20000201' as datetime) as Start UNION ALL
select 'A' as CD,cast('20000301' as datetime) as Start UNION ALL
select 'A' as CD,cast('20000401' as datetime) as Start UNION ALL
select 'A' as CD,cast('20000501' as datetime) as Start UNION ALL
select 'A' as CD,cast('20000601' as datetime) as Start UNION ALL
select 'A' as CD,cast('20000701' as datetime) as Start UNION ALL
select 'A' as CD,cast('20000801' as datetime) as Start UNION ALL
select 'A' as CD,cast('20000901' as datetime) as Start UNION ALL
select 'A' as CD,cast('20001001' as datetime) as Start UNION ALL
select 'A' as CD,cast('20001101' as datetime) as Start UNION ALL
select 'A' as CD,cast('20001201' as datetime) as Start UNION ALL
select 'B' as CD,cast('20000101' as datetime) as Start UNION ALL
select 'B' as CD,cast('20000201' as datetime) as Start UNION ALL
select 'B' as CD,cast('20000301' as datetime) as Start UNION ALL
select 'B' as CD,cast('20000401' as datetime) as Start UNION ALL
select 'B' as CD,cast('20000501' as datetime) as Start UNION ALL
select 'B' as CD,cast('20000601' as datetime) as Start UNION ALL
select 'B' as CD,cast('20000701' as datetime) as Start UNION ALL
select 'B' as CD,cast('20000801' as datetime) as Start UNION ALL
select 'B' as CD,cast('20000901' as datetime) as Start UNION ALL
select 'B' as CD,cast('20001001' as datetime) as Start UNION ALL
select 'B' as CD,cast('20001101' as datetime) as Start UNION ALL
select 'B' as CD,cast('20001201' as datetime) as Start
)
select 
 a.CD
,a.Start as 開始
,isnull(b.Start,'99991231') as 終了
from (
select 
ROW_NUMBER() OVER (PARTITION BY cd ORDER BY Start) as seq
,CD
,Start
from Data
) a
left outer join (
select 
ROW_NUMBER() OVER (PARTITION BY cd ORDER BY Start) as seq
,CD
,Start
from Data
) b
on  b.CD = a.CD
and b.Seq = a.Seq + 1
where a.CD = 'A'

--select
/* カンマで区切られてデータが入っているフィールドを別々のフィールドに分割 */
with CSVDATA as (
select '1,2,3,4,5,666,7,8,9,0' as csv
union all
select '2,2,3,4,5,6,7,8,9,0' as csv
union all
select '3,2,3,4,5,6,7,8,9,0' as csv
union all
select '4,2,3,4,5,6,7,8,9,0' as csv
union all
select '4,2,3,4,5,6,7,8,9,0' as csv
)
,
Row(p) AS (
 select 1 as p
 UNION ALL
 select p+1 as p from Row
 where p < 32767
)
select 
 no
,csv
,max(case when seq = 01 then val else null end) as val001
,max(case when seq = 02 then val else null end) as val002
,max(case when seq = 03 then val else null end) as val003
,max(case when seq = 04 then val else null end) as val004
,max(case when seq = 05 then val else null end) as val005
,max(case when seq = 06 then val else null end) as val006
,max(case when seq = 07 then val else null end) as val007
,max(case when seq = 08 then val else null end) as val008
,max(case when seq = 09 then val else null end) as val009
,max(case when seq = 10 then val else null end) as val010
,max(case when seq = 12 then val else null end) as val012
,max(case when seq = 13 then val else null end) as val013
,max(case when seq = 14 then val else null end) as val014
,max(case when seq = 15 then val else null end) as val015
,max(case when seq = 16 then val else null end) as val016
,max(case when seq = 17 then val else null end) as val017
from (
  select
   no
  ,substring(csv,2,Len(csv)-2) as csv
  ,ROW_NUMBER() OVER (PARTITION BY no,csv ORDER BY p) as seq
  ,replace(substring(csv,p+1,charindex(',',substring(csv,p+1,len(csv)))),',','') as val
  from (
    select
     ROW_NUMBER() OVER (ORDER BY csv) as no  --データを個別に識別するために必要
    ,','+csv+',' as csv                      --処理しやすいように対象を加工
    from CSVDATA
  ) as CSVDATA
  left outer join Row
  on substring(csv,p,1)=','
) CSVDATA
group by 
 no
,csv
option (MAXRECURSION 32767)

--select
/* CSV形式のデータを各フィールドに分解 */
with 
dt as (                                                                  --サンプルデータ
  select '"",123,456,"",789,"ABC","E,"","",FG","HIJ",0' as csv union all --サンプルデータ
  select '"",000,456,"",789,"ABC","E,"","",FG","HIJ",0' as csv union all --サンプルデータ
  select '"",999,456,"",789,"ABC","E,"","",FG","HIJ",0' as csv union all --サンプルデータ
  select '"",789,456,"",789,"ABC","E,"","",FG","HIJ",0' as csv union all --サンプルデータ
  select '"",123,456,"",789,"ABC","E,"","",FG","HIJ",0' as csv           --サンプルデータ
),                                                                       --サンプルデータ
Row(RowNo,csv,s,e,csv_z) AS (
  select
   RowNo
  ,csv
  ,1 as s
  ,case
   when substring(csv,1,2) = ',"' then charindex('",',replace('  '+substring(csv,1+2,len(csv)),'""','^^'))
   else                                charindex(',' ,replace(     substring(csv,1+1,len(csv)),',' ,',' ))
   end + 1 as e
  ,substring(csv,charindex(',',csv),len(csv)) as csv_z 
  from (
    select
     ROW_NUMBER() OVER (ORDER BY csv) as RowNo
    ,','+csv+',' as csv 
    from dt
  ) dt
  UNION ALL
  select
   RowNo
  ,csv
  ,e as s
  ,case
   when substring(csv,e,2) = ',"' then charindex('",',replace('  '+substring(csv,e+2,len(csv)),'""','^^'))
   else                                charindex(',' ,replace(     substring(csv,e+1,len(csv)),',' ,',' ))
   end + e as e
  ,substring(csv,charindex(',',csv,e+1),len(csv)) as csv_z
  from Row
  where charindex(',',csv,e+1) <> 0
),
CsvData as (
  select
   RowNo
  ,csv
  ,max(case when ColNo = 001 then val end) as val001
  ,max(case when ColNo = 002 then val end) as val002
  ,max(case when ColNo = 003 then val end) as val003
  ,max(case when ColNo = 004 then val end) as val004
  ,max(case when ColNo = 005 then val end) as val005
  ,max(case when ColNo = 006 then val end) as val006
  ,max(case when ColNo = 007 then val end) as val007
  ,max(case when ColNo = 008 then val end) as val008
  ,max(case when ColNo = 009 then val end) as val009
  ,max(case when ColNo = 010 then val end) as val010
  ,max(case when ColNo = 011 then val end) as val011
  ,max(case when ColNo = 012 then val end) as val012
  ,max(case when ColNo = 013 then val end) as val013
  ,max(case when ColNo = 014 then val end) as val014
  ,max(case when ColNo = 015 then val end) as val015
  ,max(case when ColNo = 016 then val end) as val016
  ,max(case when ColNo = 017 then val end) as val017
  ,max(case when ColNo = 018 then val end) as val018
  ,max(case when ColNo = 019 then val end) as val019
  ,max(case when ColNo = 020 then val end) as val020
  ,max(case when ColNo = 021 then val end) as val021
  ,max(case when ColNo = 022 then val end) as val022
  ,max(case when ColNo = 023 then val end) as val023
  ,max(case when ColNo = 024 then val end) as val024
  ,max(case when ColNo = 025 then val end) as val025
  ,max(case when ColNo = 026 then val end) as val026
  ,max(case when ColNo = 027 then val end) as val027
  ,max(case when ColNo = 028 then val end) as val028
  ,max(case when ColNo = 029 then val end) as val029
  ,max(case when ColNo = 030 then val end) as val030
  from (
    select
     RowNo
    ,ROW_NUMBER() OVER (PARTITION BY RowNo ORDER BY s) as ColNo
    ,csv
    ,s
    ,e
    ,case 
     when substring(csv,s+1,1)='"' then replace(substring(csv,s+2,e-s-3),'""','"')
     else                               substring(csv,s+1,e-s-1)
     end as val
    from Row
  ) a
  group by RowNo,csv
)
select
 * 
from CsvData
order by RowNo
option (MAXRECURSION 10000)