February 28, 2019 at 12:02 pm
Hi
I try to get fieldnames from the subquery to print a script for adding default constraints to all tables.
It does not work with while, do I need cursors?
Set NoCount ON
Declare @schemaName varchar(100)
set @schemaName='dbo'
Declare @tableName varchar(100)
set @tableName=''
Declare @fieldName varchar(100)
set @fieldName=''
Declare @fieldType varchar(100)
set @fieldType=''
While
(
SELECT SQLNAME
FROM SQLDICTIONARY
where FIELDID = 0
and SQLNAME like 'Accountant%'
)
Begin
select @tableName = SQLNAME from SQLDICTIONARY
While
(
SELECT @fieldName=t.SQLNAME, @fieldType=t.FIELDTYPE
FROM SQLDICTIONARY t
where FIELDID <> 0
and SQLNAME = @tableName
Begin
If @fieldType = 0 --string
print 'ALTER TABLE [' + @schemaName + '].[' + @tableName +'] ADD DEFAULT ('') FOR [' + @fieldName + ']'
Else If (@fieldType = 1 Or @fieldType = 49) -- int
print ''
Else If @fieldType = 3 -- date
print ''
Else If @fieldType = 6 -- datetime
print ''
Else
print 'none'
End
)
End
Set NoCount off
February 28, 2019 at 12:36 pm
JanM-66 - Thursday, February 28, 2019 12:02 PMHi
I try to get fieldnames from the subquery to print a script for adding default constraints to all tables.
It does not work with while, do I need cursors?
Set NoCount ONDeclare @schemaName varchar(100)
set @schemaName='dbo'
Declare @tableName varchar(100)
set @tableName=''
Declare @fieldName varchar(100)
set @fieldName=''
Declare @fieldType varchar(100)
set @fieldType=''While
(
SELECT SQLNAME
FROM SQLDICTIONARY
where FIELDID = 0
and SQLNAME like 'Accountant%'
)
Begin
select @tableName = SQLNAME from SQLDICTIONARYWhile
(
SELECT @fieldName=t.SQLNAME, @fieldType=t.FIELDTYPE
FROM SQLDICTIONARY t
where FIELDID <> 0
and SQLNAME = @tableName
Begin
If @fieldType = 0 --string
print 'ALTER TABLE [' + @schemaName + '].[' + @tableName +'] ADD DEFAULT ('') FOR [' + @fieldName + ']'
Else If (@fieldType = 1 Or @fieldType = 49) -- int
print ''
Else If @fieldType = 3 -- date
print ''
Else If @fieldType = 6 -- datetime
print ''
Else
print 'none'
End
)
EndSet NoCount off
It looks like you are attempting to create a default of an empty string on every character column in your database, correct?
The question is WHY. What does an empty string do in your data that a NULL does not?
This may be easier, without knowing the schema and a sample set of data for the table SQLDICTIONARY.
SELECT
'ALTER TABLE [' + TABLE_SCHEMA + '].[' + TABLE_NAME + '] ADD CONSTRAINT DF_' + TABLE_NAME + '_' + COLUMN_NAME + ' DEFAULT ('''') FOR ' + COLUMN_NAME + ';'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN ('char', 'nchar', 'varchar', 'nvarchar')
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
February 28, 2019 at 2:18 pm
Thanks. Yes default values for the different types of an application table structure. Fields are defined not null, all do need a defaul value.
The standard command ALTER TABLE [dbo].[ACCOUNTANT_BR] ADD DEFAULT ('') FOR [CRCCOUNTRYREGIONID] will produce the constraint name DF__ACCOUNTAN__CRCCO__0866ECE0. Is this to limit long tabel/field names?
February 28, 2019 at 2:28 pm
JanM-66 - Thursday, February 28, 2019 2:18 PMThanks. Yes default values for the different types of an application table structure. Fields are defined not null, all do need a defaul value.
The standard command ALTER TABLE [dbo].[ACCOUNTANT_BR] ADD DEFAULT ('') FOR [CRCCOUNTRYREGIONID] will produce the constraint name DF__ACCOUNTAN__CRCCO__0866ECE0. Is this to limit long tabel/field names?
No, SQL makes up names so as not to conflict with other object names.
If you look at the code I posted, it provides a name for the constraints.
Curiously, and this may create a debate, but why is everything not null? Like everything, there is good an less than good usage for various things.
Creating defaults to insert empty strings for every character column seems like a not so good idea.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply