SQL Serverのテーブル or ビューの定義から C#のデータアクセス用classを作成 revision2

 SQL Serverのテーブル or ビューの定義から C#のデータアクセス用classを作成 revision2

T-SQLのREAL項目を追加

 

 SELECT 'public ' +
      CASE
      WHEN UPPER(DATA_TYPE) IN ('INT')      THEN 'int'
      WHEN UPPER(DATA_TYPE) IN ('SMALLINT') THEN 'short'
      WHEN UPPER(DATA_TYPE) IN ('FLOAT')    THEN 'double'
      WHEN UPPER(DATA_TYPE) IN ('REAL')     THEN 'float'
      WHEN UPPER(DATA_TYPE) IN ('TINYINT')  THEN 'sbyte'
      WHEN UPPER(DATA_TYPE) IN ('BIT')      THEN 'bool'
      WHEN UPPER(DATA_TYPE) IN ('DATE','DATETIME')  THEN 'DateTime'
      WHEN UPPER(DATA_TYPE) IN ('CHAR','NCHAR','VARCHAR','NVARCHAR','TEXT','NTEXT') THEN 'string'
      ELSE 'xx' END +
      CASE WHEN IS_NULLABLE = 'NO' THEN ' ' ELSE '?' END +
      COLUMN_NAME +
      ' { get; set; }'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
ORDER BY ORDINAL_POSITION

最後に CHAR(13)があって、余分な改行を削除しました

SQL Serverのテーブル or ビューの定義から C#のデータアクセス用classを作成

 T-SQLで作成

 

--ここからC#用classを作成
DECLARE @TableName VARCHAR(30)

SET @TableName = 'dummytable'

SELECT 'public ' +
      CASE
      WHEN UPPER(DATA_TYPE) IN ('INT')      THEN 'int'
      WHEN UPPER(DATA_TYPE) IN ('SMALLINT') THEN 'short'
      WHEN UPPER(DATA_TYPE) IN ('FLOAT')    THEN 'double'
      WHEN UPPER(DATA_TYPE) IN ('TINYINT')  THEN 'sbyte'
      WHEN UPPER(DATA_TYPE) IN ('BIT')      THEN 'bool'
      WHEN UPPER(DATA_TYPE) IN ('DATE','DATETIME')  THEN 'DateTime'
      WHEN UPPER(DATA_TYPE) IN ('CHAR','NCHAR','VARCHAR','NVARCHAR','TEXT','NTEXT') THEN 'string'
      ELSE 'xx' END +
      CASE WHEN IS_NULLABLE = 'NO' THEN ' ' ELSE '?' END +
      COLUMN_NAME +
      ' { get; set; }' + CHAR(13)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
ORDER BY ORDINAL_POSITION
 

１００近い項目の変換が面倒になった

SQL Sever 統計情報の明示的な更新

統計情報の明示的な更新は、「UPDATE STATISTICS」コマンドで行います。

MS-SQL テーブル情報の取得

SELECT * FROM syscolumns

WHERE id = object_id('テーブル名')
order by colorder

MS-SQL / SQL Server / fnReplaceZenkakuToHankaku 全角の文字列を半角に変換する方法

CREATE FUNCTION [dbo].[fnReplaceZenkakuToHankaku]

(

@StrVal nvarchar(100)

)

RETURNS nvarchar(100)

BEGIN

DECLARE @Result nvarchar(100)

DECLARE @TempStr nchar(1)

DECLARE @i int

DECLARE @UniCode int

SET @Result = ''

SET @i = 1

WHILE (@i <= LEN(@StrVal))

BEGIN

SET @UniCode = UNICODE(SUBSTRING(@StrVal,@i,1))

IF (@UniCode = 12288) --空欄の場合。

SET @TempStr = N''

ELSE IF ((@UniCode >= 65280) AND (@UniCode <= 65519)) --全角の場合。

SET @TempStr = NCHAR(@UniCode-65248) 

ELSE 

SET @TempStr = SUBSTRING(@StrVal,@i,1)

SET @Result = @Result + @TempStr

SET @i = @i + 1

END

RETURN @Result

END

 

元ネタ http://www.innoya.com/Board/ViewTip.aspx?menuID=4&Idx=200

dbMAGIC MS-SQLのIDENTITY項目のDBタイプ

INTEGER IDENTITY

MS-SQL テンポラリテーブル（一時テーブル）の存在確認と削除

IF OBJECT_ID(N'tempdb..#TestLocalTempTable', N'U') IS NOT NULL
DROP TABLE #TestLocalTempTable;

sqlcmdの使い方

sqlcmdの使い方

sqlcmd -S DBインスタンス -U ユーザーID -P パスワード -i sqlスクリプト

例
sqlcmd -S sv-sql2014\express -U develop -P kaihatu123 -i c:\temp\run.sql


オプション -E を使うと、Windpows認証になるので、 -U -Pは不要

SQL Serverの移行で使用 DatabaseをSQLに変換

スクリプトを出力 -> 出力項目を選択 -> 詳細オプション -> スクリプトを作成するデータの種類を
スキーマ から スキーマとデータ 
に変更する。

MS-SQL identityの項目にInsertする方法

set identity_insert [テーブル名] on;
insert into [テーブル名] ([ID], [Value]) values (1, 1)
set identity_insert [テーブル名] off;

MS-SQL 漢字を含む文字列の分割

SQL-Serverが全角文字を最後の一文字で断ち割った時は、指定バイト数よりも1バイト欠損するという性質を利用して、判定に使います。 ALTER VIEW [dbo].[v最近商品一覧] (得意先CD, 出荷年月日, 出荷伝票番号, 行番号, 商品CD, 商品名, 商品名1, 商品名2, 補足, 数量, 単価, 金額 ) AS SELECT [得意先CD] ,[出荷年月日] ,[出荷伝票番号] ,[行番号] ,[商品CD] ,[商品名] ,SUBSTRING(CAST(RTRIM(LTRIM([商品名])) AS TEXT),1,20) ,CASE WHEN DATALENGTH(SUBSTRING(CAST(RTRIM(LTRIM([商品名])) AS TEXT),1,20)) < 20 THEN SUBSTRING(CAST(RTRIM(LTRIM([商品名])) AS TEXT),20,21) ELSE SUBSTRING(CAST(RTRIM(LTRIM([商品名])) AS TEXT),21,20) END ,[補足] ,[数量] ,[単価] ,[金額] FROM dbo.e_KAKOSR_DAT AS D1

ＳＱＬ Ｓｅｖｅｒのテーブルが開けない

現象
ＳＱＬ　Ｓｅｖｅｒのテーブルが開けない

理由
select出来ないテーブルがテーブルlockになっていた。

対応
lockされているテーブルのsessionをkill

念のため、indexをrebuildしておいた

また、lockなしで参照する方法

select * from table_name with(nolock)

MAGIC MS-SQL 分離レベルを１

MAGIC MS-SQL 分離レベルを１にする

redmineのデータベースをMS-SQLへ移行

redmineのデータベースをMS-SQLに移行する
bitnamiで構築していたのだが、毎回データベースの構築をするのが面倒になってきた

OSがWindowsかつ、開発用として、ＰＣ上にSQL Expressが動作しているから
MySQLを廃止する。

redmineを動作させる環境は、JRuby + MS-SQL expressにする

JRubyのversion は 1.7.18
Redmineのversionは、2.6.1
JAVAのversionは、1.8.25

MS-SQL用JDBC Driver version sqljdbc_4.0.2206.100_jpn

MySQLのODBCドライバを使ってMS-SQL SQL ManagementのData Importからデータを移行

sql server JDBC Driveは sql4を指定

sql server JDBC Driveは sql4を指定

Microsoft からjdbc driverをdownload

環境変数のCLASSPATHに上記 jdbc driverを追加

SQL Serverインスタンスでコンピュータの名前変更を行ったらやっておくこと。

変更点は２つ

１．SQL Serverの設定変更
２．Reporting Servicesの設定変更

次は更新作業を行います。
*コンピュータ名は自環境に読み替えてください。
旧コンピュータ名：WIN-SQL2014
新コンピュータ名：SV-SQL2014

sql発行

sp_dropserver 'WIN-SQL2014';
GO

sp_addserver 'SV-SQL2014', local;
GO

確認します

SELECT @@SERVERNAME AS 'Server Name';

次にデータベースのメニューに移動し「データベースの変更：を行います。

「既存のレポートサーバーのデータベースを選択する」を選択します。

レポートサーバーデータベースは「ReportServer」を選択します。

元ネタ

http://dev.classmethod.jp/cloud/aws/sql-server/

SQL Serverで数値の 9 を文字列 '09' にフォーマットする

SQL Serverで数値の 9 を文字列 '09' にフォーマットする

Transact-SQL にはこれをする関数がないらしい。
なので、RIGHTとCONVERTを組み合わせるのだそうだ。

select right('00' + convert(varchar, 9), 2)

SQL Server Agent XPs

ＳＱＬ　ＳｅｒｖｅｒのMainTenance PLanが起動しない。

SQL Server Agent XPs

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Agent XPs', 1;
GO
RECONFIGURE
GO

MS-SQL SERVERの分析

ORACLEのANALYEの相当

EXEC sp_updatestats;

SQL Server(T-SQL)で、HTML形式で保存されている項目から文字列を抽出

SQL Server(T-SQL)で、HTML形式で保存されている項目から文字列を抽出

業務連絡として、HTML Editorの値をNVARCHAR(MAX)の項目で保存
桁数がMAXなので、SUBTRONGで短く５０桁にしたら
タグなども無視したので、正しくHTML表示されなかった。

CREATE FUNCTION [dbo].[udf_StripHTML] (@HTMLText VARCHAR(MAX))
RETURNS VARCHAR(MAX) AS
BEGIN
    DECLARE @Start INT
    DECLARE @End INT
    DECLARE @Length INT
    SET @Start = CHARINDEX('<',@HTMLText)
    SET @End = CHARINDEX('>',@HTMLText,CHARINDEX('<',@HTMLText))
    SET @Length = (@End - @Start) + 1
    WHILE @Start > 0 AND @End > 0 AND @Length > 0
    BEGIN
        SET @HTMLText = STUFF(@HTMLText,@Start,@Length,'')
        SET @Start = CHARINDEX('<',@HTMLText)
        SET @End = CHARINDEX('>',@HTMLText,CHARINDEX('<',@HTMLText))
        SET @Length = (@End - @Start) + 1
    END
    RETURN LTRIM(RTRIM(@HTMLText))
END
GO

NVARCHARで返したい場合は

CREATE FUNCTION [dbo].[udf_StripHTML] (@HTMLText NVARCHAR(MAX))
RETURNS NVARCHAR(MAX) AS
BEGIN
    DECLARE @Start INT
    DECLARE @End INT
    DECLARE @Length INT
    SET @Start = CHARINDEX('<',@HTMLText)
    SET @End = CHARINDEX('>',@HTMLText,CHARINDEX('<',@HTMLText))
    SET @Length = (@End - @Start) + 1
    WHILE @Start > 0 AND @End > 0 AND @Length > 0
    BEGIN
        SET @HTMLText = STUFF(@HTMLText,@Start,@Length,'')
        SET @Start = CHARINDEX('<',@HTMLText)
        SET @End = CHARINDEX('>',@HTMLText,CHARINDEX('<',@HTMLText))
        SET @Length = (@End - @Start) + 1
    END
    RETURN LTRIM(RTRIM(@HTMLText))
END
GO 

VARCHARをNVARCHARに変更すればＯＫ

使い方

SUBSTRING([dbo].[udf_StripHTML](A.[Comment]),1,50),