January 16, 2012 at 11:50 pm
Is This True ??
USE TestDb;
DECLARE @Table NVARCHAR(MAX),
@Col NVARCHAR(MAX)
DECLARE Table_Cursor CURSOR
FOR
SELECT a.name, --table
b.name --col
FROM sysobjects a,
syscolumns b
WHERE a.id = b.id
AND a.xtype = 'u' --User table
AND (
b.xtype = 104 --bit
)
OPEN Table_Cursor FETCH NEXT FROM Table_Cursor INTO @Table,@Col
WHILE (@@FETCH_STATUS = 0)
BEGIN
EXEC (
'update [' + @Table + '] set [' + @Col + ']=False
Where ([' + @Col + '] IS NULL)
FETCH NEXT FROM Table_Cursor INTO @Table,@Col
END CLOSE Table_Cursor DEALLOCATE Table_Cursor
January 16, 2012 at 11:58 pm
Hi babak,
Have you tried my reply? I dont have to use CURSOR HECK to execute the command. And its pretty short and clear I think. 🙂
"Often speak with code not with word,
A simple solution for a simple question"
January 16, 2012 at 11:59 pm
This will generate a script that takes into account the main things I can think of immediately. I would modify it to use a cursor, add error handling, possibly log backups...that sort of thing. Definitely test before you run anything at all.
SELECT
N'
UPDATE ' + QUOTENAME(s.NAME) + N'.' + QUOTENAME(t.name) + N'
SET ' + QUOTENAME(c.name) + N' = CONVERT(bit, 0)
WHERE
' + QUOTENAME(c.name) + N'IS NULL;
GO'
FROM sys.tables AS t
JOIN sys.schemas AS s ON
s.schema_id = t.schema_id
JOIN sys.columns AS c ON
c.object_id = t.object_id
JOIN sys.types AS ty
ON ty.system_type_id = c.system_type_id
WHERE
ty.name = N'bit'
AND c.is_nullable = CONVERT(bit, 'true')
AND c.is_computed = CONVERT(bit, 'false')
AND t.is_ms_shipped = CONVERT(bit, 'false');
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 17, 2012 at 12:02 am
mhike2hale (1/16/2012)
Have you tried my reply? I dont have to use CURSOR HECK to execute the command. And its pretty short and clear I think. 🙂
It will try to update computed columns, does not account for schemas, will update columns that are already NOT NULL, does not exclude system tables, will fail for columns with embedded spaces...short and clear though 😛
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 17, 2012 at 12:04 am
Hi SSC Rookie
Thanks For Reply 😉
I dont Test your Query yet
January 17, 2012 at 12:06 am
You will also need to alter each column definition to NOT NULL once each table has been 'fixed'.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 17, 2012 at 12:15 am
Hi Paul,
Yeah you're right! Thanks for your observation 😀
"Often speak with code not with word,
A simple solution for a simple question"
January 17, 2012 at 12:18 am
mhike2hale (1/17/2012)
Yeah you're right! Thanks for your observation 😀
No worries; I doubt mine is perfect either 🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 17, 2012 at 12:26 am
Dont Work Both Query
SELECT
N'
UPDATE ' + QUOTENAME(s.NAME) + N'.' + QUOTENAME(t.name) + N'
SET ' + QUOTENAME(c.name) + N' = CONVERT(bit, 0)
WHERE
' + QUOTENAME(c.name) + N'IS NULL;
GO'
FROM sys.tables AS t
JOIN sys.schemas AS s ON
s.schema_id = t.schema_id
JOIN sys.columns AS c ON
c.object_id = t.object_id
JOIN sys.types AS ty
ON ty.system_type_id = c.system_type_id
WHERE
ty.name = N'bit'
AND c.is_nullable = CONVERT(bit, 'true')
AND c.is_computed = CONVERT(bit, 'false')
AND t.is_ms_shipped = CONVERT(bit, 'false');
and Also
declare @queries varchar(max)
set @queries = ''
select @queries = @queries + 'update ' + b.name + ' set ' + a.name + ' = 0 where ' + a.name + ' is null' + char(10)
from sys.columns a
inner join sys.tables b on a.object_id = b.object_id
inner join sys.types c on a.system_type_id = c.system_type_id
where b.name != 'sysdiagrams'
and c.name = 'bit'
order by b.name, a.name
print @queries--to verify the queries to be executed
January 17, 2012 at 12:32 am
babak3334000 (1/17/2012)
Dont Work Both Query
Explain.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 17, 2012 at 12:42 am
Plz Download Attach Test Databace
And Run Your Query
January 17, 2012 at 12:49 am
My script works.
I think you did not execute this line:
exec(@queries)--execute your query
This will execute the generated scripts and make changes to your database.
"Often speak with code not with word,
A simple solution for a simple question"
January 17, 2012 at 12:51 am
babak3334000 (1/17/2012)
Plz Download Attach Test Databace And Run Your Query
Done. Mine produces this script:
UPDATE [dbo].[Table_1]
SET [ch1] = CONVERT(bit, 0)
WHERE
[ch1]IS NULL;
GO
UPDATE [dbo].[Table_1]
SET [ch2] = CONVERT(bit, 0)
WHERE
[ch2]IS NULL;
GO
UPDATE [dbo].[Table_2]
SET [ch3] = CONVERT(bit, 0)
WHERE
[ch3]IS NULL;
GO
UPDATE [dbo].[Table_2]
SET [ch4] = CONVERT(bit, 0)
WHERE
[ch4]IS NULL;
GO
And gives this output when executed:
(5 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(6 row(s) affected)
What do you think is not working? Neither script was intended to actually make the changes - that's up to you to review, test, and implement.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 17, 2012 at 12:58 am
mhike2hale (1/17/2012)
My script works.I think you did not execute this line:
exec(@queries)--execute your queryThis will execute the generated scripts and make changes to your database.
Ok very Good
I quickly did I forget it
January 17, 2012 at 1:04 am
Paul's script works either, you just have to implement the generated scripts to make changes.
"Often speak with code not with word,
A simple solution for a simple question"
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply