189 results found

1. Expand a view or views referenced in a query

   I think it would be great if SQL Prompt had the functionality to expand a view that is used in a query. So if you have this schema:
   CREATE TABLE dbo.Person
   (
   PersonID INT IDENTITY(1, 1)
   PRIMARY KEY,
   LastName VARCHAR(30),
   FirstName VARCHAR(30),
   );
   GO

   CREATE TABLE dbo.Student
   (
   StudentID INT IDENTITY(1, 1)
   PRIMARY KEY,
   PersonID INT,
   StudentNo VARCHAR(100)
   );
   GO

   CREATE TABLE dbo.Class
   (
   ClassID INT IDENTITY(1, 1)
   PRIMARY KEY,
   ClassName VARCHAR(100)
   );
   GO

   CREATE TABLE dbo.ClassRoster
   (
   ClassID INT,
   StudentID INT
   );

   GO

   CREATE VIEW dbo.Students
   AS
   SELECT
   S.StudentID,
   P.LastName,
   P.FirstName,
   S.StudentNo
   FROM
   dbo.Person AS P
   JOIN dbo.Student AS S
   ON P.PersonID = S.PersonID;
   GO

   CREATE VIEW dbo.Classes
   AS
   SELECT
   C.ClassID,
   C.ClassName,
   CR.StudentID
   FROM
   dbo.Class AS C
   JOIN dbo.ClassRoster AS CR
   ON C.ClassID = CR.ClassID;
   GO

   Then you have this query:

   SELECT
   S.StudentID,
   S.LastName,
   S.FirstName,
   S.StudentNo,
   C.ClassID,
   C.ClassName,
   C.StudentID
   FROM
   dbo.Students AS S
   JOIN Classes AS C
   ON S.StudentID = C.StudentID;

   You could right-click or have a keyboard combination that say Expand Views and the query becomes:

   SELECT
   S.StudentID,
   S.LastName,
   S.FirstName,
   S.StudentNo,
   C.ClassID,
   C.ClassName,
   C.StudentID
   FROM
   (
   SELECT
   S.StudentID,
   P.LastName,
   P.FirstName,
   S.StudentNo
   FROM
   dbo.Person AS P
   JOIN dbo.Student AS S
   ON P.PersonID = S.PersonID
   ) AS S
   JOIN (
   SELECT
   C.ClassID,
   C.ClassName,
   CR.StudentID
   FROM
   dbo.Class AS C
   JOIN dbo.ClassRoster AS CR
   ON C.ClassID = CR.ClassID
   ) AS C
   ON S.StudentID = C.StudentID;

   I would find this useful when working with third party products or when coming in new to an environment as it would immediately show me the places where there are multiple references to the same table or where a view is providing some layer of filtering that I'm not aware of.

   I think it would be great if SQL Prompt had the functionality to expand a view that is used in a query. So if you have this schema:
   CREATE TABLE dbo.Person
   (
   PersonID INT IDENTITY(1, 1)
   PRIMARY KEY,
   LastName VARCHAR(30),
   FirstName VARCHAR(30),
   );
   GO

   CREATE TABLE dbo.Student
   (
   StudentID INT IDENTITY(1, 1)
   PRIMARY KEY,
   PersonID INT,
   StudentNo VARCHAR(100)
   );
   GO

   CREATE TABLE dbo.Class
   (
   ClassID INT IDENTITY(1, 1)
   PRIMARY KEY,
   ClassName VARCHAR(100)
   );
   GO

   CREATE TABLE dbo.ClassRoster
   (
   ClassID INT,
   StudentID INT
   );

   GO

   CREATE VIEW dbo.Students
   AS
   SELECT
   S.StudentID,
   P.LastName,
   P.FirstName,
   S.StudentNo
   FROM
   dbo.Person AS P
   JOIN dbo.Student AS… more

   17 votes

   Vote Vote Vote

   We're glad you're here

   Please sign in to leave feedback

   Signed in as (Sign out)

   Close

   Close

   Vote

   We’ll send you updates on this idea

   3 comments  ·  Format SQL  ·  Delete…  ·  Admin →
   How important is this to you?

   Not at all You must login first! Important You must login first! Critical You must login first!

   We're glad you're here

   Please sign in to leave feedback

   Signed in as (Sign out)

   Close

   Close

   Submit Rating

2. Format SQL Code on opening file

   It would be awsome if there was an option that would format an SQL Script automatically after opening it in SSMS / VS. So if you load a file it would be formatted automatically with your selected formatting style. It would be great too if this auto format option could be switched on and off in the Options.

   17 votes

   Vote Vote Vote

   We're glad you're here

   Please sign in to leave feedback

   Signed in as (Sign out)

   Close

   Close

   Vote

   We’ll send you updates on this idea

   2 comments  ·  Format SQL  ·  Delete…  ·  Admin →
   How important is this to you?

   Not at all You must login first! Important You must login first! Critical You must login first!

   We're glad you're here

   Please sign in to leave feedback

   Signed in as (Sign out)

   Close

   Close

   Submit Rating

3. Replace all tabs with spaces

   Setting the option to use spaces instead of tabs for intending (our house policy) only works for new tab presses.
   I would like a simple action that would replace all tabs in the current document with spaces -- using the tab-to-spaces value as set in options. Sublime Text has this and I use practically every time I edit anything.

   57 votes

   Vote Vote Vote

   We're glad you're here

   Please sign in to leave feedback

   Signed in as (Sign out)

   Close

   Close

   Vote

   We’ll send you updates on this idea

   3 comments  ·  Format SQL  ·  Delete…  ·  Admin →
   How important is this to you?

   Not at all You must login first! Important You must login first! Critical You must login first!

   We're glad you're here

   Please sign in to leave feedback

   Signed in as (Sign out)

   Close

   Close

   Submit Rating

4. Format all SQL in an SSDT project.
   16 votes

   Vote Vote Vote

   We're glad you're here

   Please sign in to leave feedback

   Signed in as (Sign out)

   Close

   Close

   Vote

   We’ll send you updates on this idea

   3 comments  ·  Format SQL  ·  Delete…  ·  Admin →
   How important is this to you?

   Not at all You must login first! Important You must login first! Critical You must login first!

   We're glad you're here

   Please sign in to leave feedback

   Signed in as (Sign out)

   Close

   Close

   Submit Rating

5. Convert code to dynamic Code

   Convert selected CODE or a complete stored procedure to Dynamic SQL Code.

   16 votes

   Vote Vote Vote

   We're glad you're here

   Please sign in to leave feedback

   Signed in as (Sign out)

   Close

   Close

   Vote

   We’ll send you updates on this idea

   3 comments  ·  Format SQL  ·  Delete…  ·  Admin →
   How important is this to you?

   Not at all You must login first! Important You must login first! Critical You must login first!

   We're glad you're here

   Please sign in to leave feedback

   Signed in as (Sign out)

   Close

   Close

   Submit Rating

6. Template based formatting with different

   Template based formatting.
   Select your text and right click, format according to template... then select the appropriate template of your choice.

   8 votes

   Vote Vote Vote

   We're glad you're here

   Please sign in to leave feedback

   Signed in as (Sign out)

   Close

   Close

   Vote

   We’ll send you updates on this idea

   0 comments  ·  Format SQL  ·  Delete…  ·  Admin →
   How important is this to you?

   Not at all You must login first! Important You must login first! Critical You must login first!

   We're glad you're here

   Please sign in to leave feedback

   Signed in as (Sign out)

   Close

   Close

   Submit Rating

7. Auto generated INSERT needs a column blacklist

   Add the ability to exclude columns from appearing in the auto-generated INSERT statements. I have to manually remove certain columns every time.

   9 votes

   Vote Vote Vote

   We're glad you're here

   Please sign in to leave feedback

   Signed in as (Sign out)

   Close

   Close

   Vote

   We’ll send you updates on this idea

   0 comments  ·  Format SQL  ·  Delete…  ·  Admin →
   How important is this to you?

   Not at all You must login first! Important You must login first! Critical You must login first!

   We're glad you're here

   Please sign in to leave feedback

   Signed in as (Sign out)

   Close

   Close

   Submit Rating

8. Align aliases, commas, datatypes and expressions at the next tab stop

   I align aliases, commas, datatypes and expressions at the first tab stop following the longest element in the list. See examples below:

   -- Tilde character used as space replacement to cope with HTML formatting
   -- 2-character tabs are assumed below
   -- In a fixed-space font, the commas, = signs, data types and AS keywords would all line up vertically
   DECLARE
   ~~@x~~~~~~~~~~~~~~INT~~~~~~~~~,
   ~~@var~~~~~~~~~~~~VARCHAR(4)~~,
   ~~@longer_varname~VARCHAR(10)~,
   ~~@z~~~~~~~~~~~~~~DATETIME~~~~;

   SELECT
   ~~~~@longer_varname~=~'x'~~~~~~~,
   ~~~~@var~~~~~~~~~~~~=~'1234'~~~~,
   ~~~~@z~~~~~~~~~~~~~~=~GETDATE()~;

   SELECT
   ~~~~T1.shortfield~~~~~~~~AS~ShortField~,
   ~~~~AO.reallylongfield~~AS~LongField~~,
   ~~~~'x'~~~~~~~~~~~~~~~~~~~AS~XChar~~~~~~,
   ~~FROM~~~~~~~~dbo.table1~~~~~~AS~T1
   ~~~~LEFT~JOIN~dbo.anotherone~AS~AO~ON~T1.keyval~=~AO.keyval;

   EXEC~@x~=~dbo.SomeProc
   ~~@Param1~=~'3'~~~~~~~~~~~,
   ~~@P2~~~~~=~'Longer~Text'~,
   ~~@P3~~~~~=~4~~~~~~~~~~~~~;

   27 votes

   Vote Vote Vote

   We're glad you're here

   Please sign in to leave feedback

   Signed in as (Sign out)

   Close

   Close

   Vote

   We’ll send you updates on this idea

   3 comments  ·  Format SQL  ·  Delete…  ·  Admin →
   How important is this to you?

   Not at all You must login first! Important You must login first! Critical You must login first!

   We're glad you're here

   Please sign in to leave feedback

   Signed in as (Sign out)

   Close

   Close

   Submit Rating

9. warn against data truncation

   I would be nice if SQL-prompt could warn against posible data truncation.
   Like assigning a int value to a tinyint field/variable or assigning a varchar(30) to a varchar(3).
   I want it both at an global analysis (like find invalid objects) and in the current scrip edit (while modifying a trigger / procedure / function).

   13 votes

   Vote Vote Vote

   We're glad you're here

   Please sign in to leave feedback

   Signed in as (Sign out)

   Close

   Close

   Vote

   We’ll send you updates on this idea

   1 comment  ·  Format SQL  ·  Delete…  ·  Admin →
   How important is this to you?

   Not at all You must login first! Important You must login first! Critical You must login first!

   We're glad you're here

   Please sign in to leave feedback

   Signed in as (Sign out)

   Close

   Close

   Submit Rating