Help us understand the problem. What is going on with this article?

変にコケて中身が空っぽのストアドを探しだすだけのクエリ

More than 1 year has passed since last update.

どんな感じよ

自作スクリプトとかで一括してストアドを流したあと、反映に失敗してるやつを見つけるためのクエリ・・・
ものすごくピンポイントでたぶん僕しか使わないですねえ。僕のよくやる失敗のケツを吹くだけのクエリですねえ。

例えば

開発環境で作成・編集したストアドを、ステージング環境へ反映だ!って時に、まさかまさか

SELECT * FROM OPENDATASOURCE('fugafuga','Data Source=hogehoge;----').dbo.SomeTable

なんて罠があったりするとコケる。
あるいは

SELECT * FROM AnotherRenamedDataBase.dbo.SomeTable

なんて言うふうに、開発環境用のデータベース名にしてて、それを変更せぬまま反映しようとしてコケたとか。

単にコケるだけならいいけど、ストアドは残るけど中身は空っぽ、ってなことになったりする。

そんなときに

SELECT   O.type,
         O.name,
         M.definition
FROM     sys.sql_modules AS M
           INNER JOIN sys.objects AS O
            ON M.object_id = O.object_id
WHERE    O.type='P'
and REPLACE(rtrim(ltrim(M.definition)), CHAR(13) + CHAR(10), '') like '%AS'
ORDER BY O.type,
         O.name;

これを使用すると、クエリが途中で切れちゃってるやつを検索出来る。

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした