6

I have already tried to alter the user defined table types but it is not working with alter commend.

alter TYPE [dbo].[GriDDateTab] AS TABLE(
    [Application [varchar](50) NOT NULL,
    [LandDist] [char](2) NULL,
    [Land] [char](3) NULL,
    [LandVi] [char](4) NULL)
Rory McCrossan
  • 331,213
  • 40
  • 305
  • 339
ganesh
  • 95
  • 1
  • 1
  • 3
  • 1
    If you have even *looked* at the [official MSDN SQL Server Books Online](https://msdn.microsoft.com/en-us/library/cc879314.aspx) - you'd see there **is no** `ALTER TYPE` statement. Just isn't there. You need to drop your type and re-create it fresh. – marc_s Apr 25 '15 at 14:21
  • Change `[Application` to `[Application]` ;). – shA.t Apr 25 '15 at 14:25
  • 1
    @marc_s - even if the OP *had* looked at the docs you cite, his question was not invalid. While the syntax "ALTER TYPE" is not listed there as an option, it would have been foolish for him to discount the idea that there was some other syntactic variation required to achieve the outcome. While there must be good reasons for the SQL devs to not allow this, It certainly does seem ridiculous on the surface that this is not possible. Being first required to drop all referencing objects is pretty draconian. – Chris Rogers Aug 15 '18 at 04:50
  • @ChrisRogers: absolutely - the lack of `ALTER TYPE` really renders the user-defined types almost useless - at least for me – marc_s Aug 15 '18 at 05:09

3 Answers3

8

You have to drop and recreate. That means if you have any references (eg stored procedures) using the type, that reference must be removed first.

dmeglio
  • 2,830
  • 1
  • 19
  • 24
1

The bad news is that you have to drop and re-create as SQL server does not (at least not up until 2012 support this.

The good news is that stored procedures and functions should pick up table type changes on the fly with no need to drop and re-create.

Ed Green
  • 128
  • 1
  • 2
  • 2
    I do not believe the good news is true, see the other answers – KCD Nov 01 '17 at 03:39
  • -- the following script demostrates that (at least from 2014 onwards it works create type t_demo as table (intfield int) go create procedure p_demo as declare @t t_demo begin select * from @t end go exec p_demo go drop type t_demo go create type t_demo as table (intfield int, textfield nvarchar(50)) go exec p_demo – Ed Green Apr 25 '18 at 08:59
  • @EdGreen - It doesn't seem to register as a dependency unless it's a parameter. EG, define your sproc this way: create procedure p_demo (@t t_demo READONLY) as begin select * from @t end go. And you'll see the problem. Meh. Not sure why. I can't find anything about dependencies ONLY being in the param defs. – jbd Jun 11 '19 at 19:05
0

Sadly everything said is true, however you can save yourself some time by by going to the context menu of the table type->Script User-Defined Table Type as..

If you use DROP and CREATE To Query Window you can make your edits and click execute.

You will still have to recreate references in other Programmability structures.

DavidRothbauer
  • 123
  • 1
  • 6