Please help with Datatypes. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Please help with Datatypes.

Author

Topic

NguyenL71
Posting Yak Master

228 Posts

Posted - 2012-02-13 : 14:33:31

[code]Hi,

I need to develope script/sp to generate all the tables in all DBs. Currently, I have 10 DBs and 300 tables for Each DB, I a script to convert SQL Server to
Advantage datetypes. Please see the desire results below. I am using SQL Server 2008.

Thank you so much in advance.

IF OBJECT_ID('dbo.MyTest', 'u') IS NOT NULL
DROP TABLE dbo.MyTest
GO

CREATE TABLE dbo.MyTest
(
CustId INT IDENTITY (1, 1) NOT NULL,
LoginId VARCHAR(50) NULL,
NMLSId INT NOT NULL,
DoseStatus SMALLINT NULL,
MaxDos NUMERIC(19, 4) NULL,
AmountPaid MONEY NULL,
StopDt SMALLDATETIME NULL,
ProcessTime DATETIME NULL,
UpdateDt DATETIME NULL,
ProcessND BIT NULL,
MyNote VARCHAR(MAX) NULL,
YourComment VARBINARY(MAX) NULL
)

go

--Convert SQL Server to Advantage datatypes:

From To:
SQL ADS
------ ----
VarChar(n) Char(n)
Int Integer
Int Identity(1,1) AutoInc
SmallInt Short
Numeric(19,4) Double
Money CurDouble
SmallDateTime Date
DateTime Time -- 5% Note: Will use time portion only in certain columns and tables.
DateTime TimeStamp -- 95%
Bit Logical
VarChar(Max) Memo
VarBinary(Max) Blob

-- Desire output:
IF OBJECT_ID('dbo.MyTest', 'u') IS NOT NULL
DROP TABLE dbo.MyTest
GO

CREATE TABLE dbo.MyTest
(
CustId AutoInc NOT NULL,
LoginId CHAR(50) NULL,
NMLSId INTEGER NOT NULL,
DoseStatus SMALLINT NULL,
MaxDos Double NULL,
AmountPaid CurDouble NULL,
StopDt Date NULL,
ProcessTime Time NULL, -- Specify column name.
UpdateDt Timestamp NULL,
ProcessND Logical NULL,
MyNote Memo NULL,
YourComment Blob NULL
)
go

-- Testing...

-- How can I change the SP to convert to Advantage datatypes when I run the sp below. Thanks in advance.

/*

SELECT '"' + CAST(COLUMN_NAME AS VARCHAR(50)) + '"'
, +
CAST (CASE
WHEN ( DATA_TYPE = 'INT' ) THEN 'Integer'
WHEN ( DATA_TYPE = 'SMALLINT' ) THEN 'Short'
WHEN ( DATA_TYPE = 'BIT' ) THEN 'Logical'
WHEN ( DATA_TYPE = 'Float' ) THEN 'Float'
WHEN ( DATA_TYPE = 'DATETIME' ) THEN 'Time'
WHEN ( DATA_TYPE = 'SMALLDATETIME' ) THEN 'DATE'
WHEN ( DATA_TYPE IN ('DECIMAL', 'NUMERIC') ) THEN 'Double'
WHEN ( DATA_TYPE = 'VARBINARY' AND CHARACTER_MAXIMUM_LENGTH = -1 ) THEN 'Blob'
WHEN ( DATA_TYPE = 'VARCHAR' AND CHARACTER_MAXIMUM_LENGTH = -1 ) THEN 'Memo'
WHEN ( DATA_TYPE = 'MONEY' ) THEN 'CurDouble'
WHEN data_type IN ('CHAR', 'VARCHAR', 'NCHAR', 'NVARCHAR') THEN data_type + '(' + RTRIM(character_maximum_length) + ')'
ELSE DATA_TYPE
END AS VARCHAR(25))

,RTRIM(CASE
WHEN IS_NULLABLE = 'Yes' THEN ''
ELSE 'NOT NULL'
END ) + ', '
--, CHARACTER_MAXIMUM_LENGTH,
FROM INFORMATION_SCHEMA.Columns
WHERE (RTRIM(TABLE_NAME) = 'agent')
ORDER BY 1 ASC
*/

IF NULLIF(object_id('dbo.usp_tablescript'), 0) > 0
DROP procedure dbo.usp_tablescript
GO

CREATE PROCEDURE dbo.usp_tablescript
(
@TableName VARCHAR(60) = NULL
)
AS
/******************************************************************************************
**
** History:
** Date Analyst Reason.
** xx/xx/xx xxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx.
**
**
********************************************************************************************/
SET NOCOUNT ON
DECLARE @cmd VARCHAR(8000),
@ord_pos INT

/*

IF (@tablename IS NULL)
BEGIN
PRINT 'Input @tablename must be entered. Please check...'
SET NOCOUNT OFF
RETURN (1)
END

IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE (TABLE_NAME = @tablename) )
BEGIN
PRINT 'Table name ' + UPPER(@tablename) + ' does not exist.'
SET NOCOUNT OFF
RETURN (1)
END

PRINT 'IF NULLIF(OBJECT_ID(''dbo.' + RTRIM(@tablename) + '''), 0) > 0'
PRINT ' DROP TABLE dbo.' + @tablename
PRINT 'go'
PRINT ' '

PRINT GETDATE()
PRINT @@Servername
PRINT db_name()
*/

IF OBJECT_ID('Tempdb..##TableName', 'u') IS NOT NULL
DROP TABLE ##TableName

CREATE TABLE ##TableName
(
TableName VARCHAR(60) NULL
)

INSERT ##TableName (TableName)
SELECT CAST(TABLE_NAME AS VARCHAR(60)) AS 'TableName'
FROM INFORMATION_SCHEMA.TABLES
WHERE (TABLE_TYPE = 'BASE TABLE')
AND TABLE_NAME NOT IN ('dtproperties', 'sysdiagrams')
AND RTRIM(TABLE_NAME) = ISNULL(@TableName, RTRIM(TABLE_NAME) )
ORDER BY TableName ASC

SET @TableName = (SELECT MIN(TableName)
FROM ##TableName)

WHILE (@TableName IS NOT NULL)
BEGIN
IF (@TableName IS NULL)
BEGIN
PRINT 'Get out the here.'
BREAK
END

PRINT ' '
PRINT 'CREATE TABLE dbo.' + @TableName
PRINT '('

SET @ord_pos = 1

WHILE (@ord_pos < (SELECT MAX(ordinal_position) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = @tablename) + 1)
BEGIN
SET @cmd = (SELECT SPACE(2) + SUBSTRING(t1.column_name,1,60) + SPACE(3) + UPPER(RTRIM(t1.data_type)) +
CASE
WHEN COLUMNPROPERTY(OBJECT_ID(t1.table_name), t1.column_name, 'ISIDENTITY') = 1 THEN ' IDENTITY'
ELSE ''
END +
CASE
WHEN t1.data_type IN('CHAR', 'VARCHAR', 'NCHAR', 'NVARCHAR') THEN '(' + RTRIM(character_maximum_length) + ')'
WHEN t1.data_type IN('DECIMAL', 'NUMERIC') THEN '(' + RTRIM(numeric_precision) + ',' + RTRIM(numeric_scale) + ')'
ELSE ''
END + SPACE(2) +
CASE
WHEN Is_Nullable = 'Yes' THEN 'NULL'
ELSE 'NOT NULL'
END +
CASE
WHEN (LEN(column_default) > 0) THEN SPACE(2) + 'DEFAULT ' + t1.column_default
ELSE ''
END +
CASE
WHEN (@ord_pos + 1) < ( (SELECT MAX(ordinal_position)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = @tablename) + 1) THEN ','
ELSE ' '
END
FROM INFORMATION_SCHEMA.COLUMNS AS t1
WHERE (t1.table_name = @tablename)
AND (t1.ordinal_position = @ord_pos) )
PRINT @cmd
SET @ord_pos = (@ord_pos + 1)
END
PRINT ')'

SET @TableName = (SELECT MIN(TableName)
FROM ##TableName
WHERE TableName > @TableName)
END
GO
-------------------------------------------------------------------------------------------------
EXECute dbo.usp_tablescript @TableName = 'MyTest'
go[/code]

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-14 : 11:15:16

What about constraints, Keys and Indexes?

I suggest you get ERWin and let it do the conversion for you

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources