April 18, 2013 at 11:54 am
I want to be able to name the default constraints that I place on the table returned from a multi-line table-valued function, however SQL Server doesn't appear to support naming the constraint in this scenario. I want to be able to do this so that schema comparisions don't get false positives when comparing the names given to these default constraints. Even though you can't specify them, SQL Server does give names to these default constraints.
I'd like to be able to do something like what's in bold below:
CREATE FUNCTION [dbo].[GetElementList]
(
@color smallint
)
RETURNS
@elementList TABLE
(
PartName VARCHAR(50),
Color VARCHAR(50),
Active INT CONSTRAINT DF_GetElementList_Active DEFAULT 0
)
AS
BEGIN
...
Body removed for brevity.
...
END
Does anybody know a way to do this, or a way to acheive the same end result?
Thanks,
MKE Data Guy
April 18, 2013 at 1:30 pm
for table variables, you can create unique constraints, but you cannot name them.
i don't think they allow default or check constraints either.
only temp tables or regular tables will allow a named constraint in the definitions.
this was the first link i found stating the same:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=131034
Lowell
April 18, 2013 at 1:43 pm
...
i don't think they allow default or check constraints either.
...
Lowell, thanks for the reply.
You can create default constraints, but it doesn't appear that you're allowed to name them. It's the naming part that I'm looking to accomplish.
Actually, right as I was notified of your reply, I was testing out using sp_rename to change the system-generated name to something more standardized. Every indication at this point is that this will work. I just need to work out how to do this on a whole database without hard-coding names.
Thanks,
MKE Data Guy
April 18, 2013 at 1:43 pm
just double checked, and i can create default constraints, but no check constraints on a table variable:
declare @mytable table
(id int identity(1,1) not null primary key,
name varchar(30) unique,
employee char(1) default ('Y') )
Lowell
April 18, 2013 at 2:12 pm
Knowledge Draftsman (4/18/2013)
...
i don't think they allow default or check constraints either.
...
Lowell, thanks for the reply.
You can create default constraints, but it doesn't appear that you're allowed to name them. It's the naming part that I'm looking to accomplish.
Actually, right as I was notified of your reply, I was testing out using sp_rename to change the system-generated name to something more standardized. Every indication at this point is that this will work. I just need to work out how to do this on a whole database without hard-coding names.
I think that would introduce concurrency issues unless you get really creative with the naming here. I don't understand why you care what the name of a default constraint is on a table variable. How is that useful?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 18, 2013 at 2:38 pm
I think that would introduce concurrency issues unless you get really creative with the naming here. I don't understand why you care what the name of a default constraint is on a table variable. How is that useful?
First, we have a naming convention that should produce a unique name for each default constraint.
Second, it's not the default on a table variable. It's the default on a table type; a small but very important difference. If this were just for a table variable that was declared somewhere in the code and then disappeared once the code was done executing, we wouldn't care. But in a multistatement table-valued function, you have to define the table type that the function returns. That type definition is persisted by SQL Server in a couple different system tables. The constraint created as part of the type definition we do care about, because we'd like those constraint names to follow the naming standard.
Thanks,
MKE Data Guy
April 18, 2013 at 3:00 pm
Knowledge Draftsman (4/18/2013)
I think that would introduce concurrency issues unless you get really creative with the naming here. I don't understand why you care what the name of a default constraint is on a table variable. How is that useful?
First, we have a naming convention that should produce a unique name for each default constraint.
Second, it's not the default on a table variable. It's the default on a table type; a small but very important difference. If this were just for a table variable that was declared somewhere in the code and then disappeared once the code was done executing, we wouldn't care. But in a multistatement table-valued function, you have to define the table type that the function returns. That type definition is persisted by SQL Server in a couple different system tables. The constraint created as part of the type definition we do care about, because we'd like those constraint names to follow the naming standard.
I would say the difference really isn't that small. 🙂
It seems like a lot of effort with little reward to me because you will have to make sure you have sp_rename either in your code or part of the process everytime you update one of these functions. I would however be interested to see what you came up with.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 18, 2013 at 3:05 pm
Looking at the CREATE TYPE statement in MSDN I don't see where it allows you to name a column constraint like you can with the CREATE TABLE statement.
Your statndards my require default contraints to be named on tables, but it is quite possibile that this won't be possible on table types, nor do I think you would want to. It may cause issues if you use the same table type more than once in a given procedure or function. I also do not see a benefit to trying to naming a default constraint on a multi-statement table valued function.
April 19, 2013 at 8:03 am
So, here's what I've come up with...
SET NOCOUNT ON;
DECLARE @MisnamedConstraints TABLE
(
OldConstraintName SYSNAME,
NewConstraintName SYSNAME,
ParentObject SYSNAME,
ColumnName SYSNAME,
ConstraintDefinition NVARCHAR(Max)
)
-- Find all constraints on multistatement table-valued functions where the
-- constraint name doesn't match the standard.
INSERT INTO @MisnamedConstraints
SELECT
DC.[name] 'Constraint Object',
'DF_' + O.[name] + '_' + C.[name] 'Standards Compliant Constraint Name',
O.[name] 'Parent Object',
C.[name] 'Column Name',
DC.[definition] 'Constraint Defintion'
FROM [sys].[default_constraints] DC
INNER JOIN [sys].[objects] O
ON DC.[parent_object_id] = O.[object_id]
INNER JOIN [sys].[columns] C
ONC.[object_id] = DC.[parent_object_id]
AND C.[column_id] = DC.[parent_column_id]
WHERE O.[type] = 'TF'
AND DC.[name] <> 'DF_' + O.[name] + '_' + C.name;
-- If there are naming collisions, remove them and output them for further research.
-- It is possible that a naming collision will be cleared by this script, and that
-- running this script a second time will not result in a collision.
DELETE MC
OUTPUT
DELETED.OldConstraintName,
DELETED.NewConstraintName,
DELETED.ParentObject,
DELETED.ColumnName,
DELETED.ConstraintDefinition
FROM [sys].[default_constraints] DC
INNER JOIN @MisnamedConstraints MC
ON DC.[name] = MC.[NewConstraintName];
-- Loop through all the non-colliding constraints and rename them to
-- the standards compliant name.
DECLARE @OldConstraintName SYSNAME;
DECLARE @NewConstraintName SYSNAME;
DECLARE @OBJECT_TYPE VARCHAR(13);SET @OBJECT_TYPE = 'OBJECT';
SELECT
@OldConstraintName = OldConstraintName,
@NewConstraintName = NewConstraintName
FROM @MisnamedConstraints;
WHILE @@RowCount > 0
BEGIN
EXECUTE sp_rename @OldConstraintName, @NewConstraintName, @OBJECT_TYPE;
DELETE
FROM @MisnamedConstraints
WHERE OldConstraintName = @OldConstraintName;
SELECT
@OldConstraintName = OldConstraintName,
@NewConstraintName = NewConstraintName
FROM @MisnamedConstraints;
END
I've done some preliminary testing and things appear to function fine after the rename.
Thanks,
MKE Data Guy
April 19, 2013 at 8:14 am
That is certainly interesting. I still think this is way over the top worried about naming conventions. This will be difficult to maintain when you have to update your procs and such.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 19, 2013 at 8:19 am
Sean Lange (4/19/2013)
That is certainly interesting. I still think this is way over the top worried about naming conventions. This will be difficult to maintain when you have to update your procs and such.
I have to agree. I really think this is taking naming conventions and standards to an extreme. If it can't be done through the normal use of DDL statements without jumping through hoops to rename them after creation, it is unnecessary effort. Document that certain structures cannot be follow the standard without extra effort and leave it at that.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply