Read metadata for data type or allows null etc

1

CREATE TABLE [dbo].[TEMP_DATA](
[CITY_NAME] nvarchar NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CITY_DATA](
[CITY_NAME] nvarchar NULL
) ON [PRIMARY]
GO
INSERT INTO TEMP_DATA
SELECT 'London'

INSERT INTO TEMP_DATA
SELECT 'London45555'

As this is just a temp table that is based on some excel coming in and loading to this, before it writes to the main Table i.e CITY_DATA which only allows 12.

I want to read the meta data

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TEMP_DATA'

I can see this is 12 for the character_maximum_length

Is there a way to read each record and also go get the 12 and then do is len(CITY_DATA) =
...schema value?

2

because you did not use the 3 tick marks (before and after your DDL) --> ` <-- we cannot tell the length of the CITY_NAME column

CREATE TABLE [dbo].[TEMP_DATA](
[CITY_NAME] nvarchar(12) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CITY_DATA](
[CITY_NAME] nvarchar(12) NULL
) ON [PRIMARY]
GO

you have

CREATE TABLE [dbo].[TEMP_DATA](
[CITY_NAME] nvarchar NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CITY_DATA](
[CITY_NAME] nvarchar NULL
) ON [PRIMARY]
GO
3

The value is in temp table is say 20.

This goes into CITY but it users put in 20 this will fail.

So i am trying to dynamically do this so it shows an error that if over x.

The table TEMP will have loads of columns.

I will check numeric, null etc by reading metadata as a pre validation script

4

If 20 it will always be 20. Are you trying to see if the data inside it has a length of x? Or the data type has length of x?

Select * from [TEMP_DATA] where Len([CITY_NAME] ) > x
5

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'CITY_DATA'
This will be 12.
Read TEMP_DATA then
If the len(city_name) > then what returned from above
Print a message...

6 

DECLARE @CITY_NAME_LEN smallint
SELECT @CITY_NAME_LEN = c.max_length
FROM sys.columns c
WHERE c.object_id = OBJECT_ID('dbo.CITY_DATA') AND c.name = 'CITY_NAME'

INSERT INTO dbo.CITY_DATA ( CITY_NAME, ... )
SELECT LEFT(CITY_NAME, @CITY_NAME_LEN), ...
FROM dbo.TEMP_DATA
7

What this will be 12?

8

Sys.columns ..ok that will be good. Thanks.

9

Yes. Don't use the INFORMATION_SCHEMA views in SQL Server. They are slower and do not have many of the columns added to the standard MS sys. views.