Find out column name, data type and maximum number of character in that column in SQL Server 2008

Yesterday I got one question about how to find column name, data_type and maximum number of character used in that column if it is varchar and if it is numeric than maximum number in that column. Basically you can find column_name and Data_type from the Information_Schema.Columns but it won’t return length of real data inserted into table so I tried to achieve it logically. Look at the script.
–using AdventureWorks database for demo
–if you don’t have AdventureWorks db
–you can use your own
use adventureworks
GO
declare @sql varchar(max), @table sysname
–going to show you demo on Person.Contact table
–in adventureworks database,
–you can change your own table name
select @table=‘Contact’, @sql=
select @sql=
case when character_maximum_length is null then
@sql+‘select
    ”’+column_name+”’ as column_name,
    ”’+data_type+”’ as data_type,
    max(‘+column_name+‘) as column_length
    from ‘+Table_Schema + ‘.’+ Table_Name+‘ union all ‘
else
    @sql+‘select
    ”’+column_name+”’ as column_name,
    ”’+data_type+”’ as data_type,
    max(len(‘+column_name+‘)) as column_length
    from ‘+Table_Schema + ‘.’+ Table_Name+‘ union all ‘
end
from information_schema.columns
where table_name=+@table+ and  data_type not in (‘xml’,‘bit’,‘datetime’,‘uniqueidentifier’)
set @sql=left(@sql,len(@sql)-9)
–print @sql
exec(@sql)
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Advertisements

Catalog View over Information_Schema in Microsoft SQL Server 2008

Basically Catalog View was first introduced in SQL Server 2005. It provided insight view of database objects. Previously way back, we used to get this kind of insight tour via system tables, system SPs or Information_Schema view. Catalog has few advantages over others.

Many of the old System SPs and System tables are removed and Information_Schema is given in newer version also but just for the backward compatibility. It will be deprecated in future version for sure. Microsoft insists to use Catalog view over Information_Schema. You can have much better functionality in Catalog view and can do almost everything which you can do with Information_Schema. Let us look at one simple example which will show you columns of all tables or for those table which meets criteria in WHERE clause in SELECT statement.

–use of catalog view
SELECT
s.name AS schema_name,
t.name AS table_name,
t.type_desc AS table_type,
c.name AS column_name,
c.column_id,
ty.name AS data_type_name,
c.is_nullable
FROM sys.schemas s
INNER JOIN sys.tables t
ON s.schema_id = t.schema_id
INNER JOIN sys.columns c
ON t.object_id = c.object_id
INNER JOIN sys.types ty
ON c.system_type_id = ty.system_type_id
AND c.user_type_id = ty.user_type_id
where s.name=‘Production’ and t.name=‘ProductReview’
GO
–use of Information_Schema
select
t.TABLE_SCHEMA as schema_name,
t.TABLE_NAME as table_name,
t.TABLE_TYPE as table_type,
c.COLUMN_NAME as column_name,
c.ORDINAL_POSITION as column_id,
c.DATA_TYPE as data_type_name,
c.IS_NULLABLE as is_nullable
from INFORMATION_SCHEMA.TABLES as t
join INFORMATION_SCHEMA.COLUMNS as c
on t.TABLE_SCHEMA=c.TABLE_SCHEMA and t.TABLE_NAME=c.TABLE_NAME
where t.TABLE_SCHEMA=‘Production’ and t.TABLE_NAME=‘ProductReview’
 BTW, I have written very small note of Catalog View in past too, if you would like to read it, Click Here.

I forced myself to write this article as I still see people are so used to with Information_Schema even this is almost 5 years SQL Server 2005 introduced and even two years SQL Server 2008 is introduced (since beta). I would suggest please stop using Information_Schema and grab the power of new Catalog View.



Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Catalog View over Information_Schema in Microsoft SQL Server 2008

Basically Catalog View was first introduced in SQL Server 2005. It provided insight view of database objects. Previously way back, we used to get this kind of insight tour via system tables, system SPs or Information_Schema view. Catalog has few advantages over others.

Many of the old System SPs and System tables are removed and Information_Schema is given in newer version also but just for the backward compatibility. It will be deprecated in future version for sure. Microsoft insists to use Catalog view over Information_Schema. You can have much better functionality in Catalog view and can do almost everything which you can do with Information_Schema. Let us look at one simple example which will show you columns of all tables or for those table which meets criteria in WHERE clause in SELECT statement.

–use of catalog view
SELECT
s.name AS schema_name,
t.name AS table_name,
t.type_desc AS table_type,
c.name AS column_name,
c.column_id,
ty.name AS data_type_name,
c.is_nullable
FROM sys.schemas s
INNER JOIN sys.tables t
ON s.schema_id = t.schema_id
INNER JOIN sys.columns c
ON t.object_id = c.object_id
INNER JOIN sys.types ty
ON c.system_type_id = ty.system_type_id
AND c.user_type_id = ty.user_type_id
where s.name=‘Production’ and t.name=‘ProductReview’
GO
–use of Information_Schema
select
t.TABLE_SCHEMA as schema_name,
t.TABLE_NAME as table_name,
t.TABLE_TYPE as table_type,
c.COLUMN_NAME as column_name,
c.ORDINAL_POSITION as column_id,
c.DATA_TYPE as data_type_name,
c.IS_NULLABLE as is_nullable
from INFORMATION_SCHEMA.TABLES as t
join INFORMATION_SCHEMA.COLUMNS as c
on t.TABLE_SCHEMA=c.TABLE_SCHEMA and t.TABLE_NAME=c.TABLE_NAME
where t.TABLE_SCHEMA=‘Production’ and t.TABLE_NAME=‘ProductReview’
 BTW, I have written very small note of Catalog View in past too, if you would like to read it, Click Here.

I forced myself to write this article as I still see people are so used to with Information_Schema even this is almost 5 years SQL Server 2005 introduced and even two years SQL Server 2008 is introduced (since beta). I would suggest please stop using Information_Schema and grab the power of new Catalog View.



Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Dynamic alter table script to make all column NULL in sql server 2005/2008

I have read question in few different forums many time that “How to make all column null in one table?” 
Answer is very small and handy dynamic script which uses “Information_Schema.columns” view to get column name and generate Alter Table script. Have a look:
select ‘alter table ‘ + ic.TABLE_SCHEMA + ‘.’ + ic.TABLE_NAME + ‘ alter column ‘ + ic.COLUMN_NAME +   + ic.DATA_TYPE 
+ case when ic.DATA_TYPE=‘varchar’ then + ‘(‘ + cast(ic.CHARACTER_MAXIMUM_LENGTH AS varchar(3)) + ‘)’ else ‘ ‘ end + ‘ ‘ + ‘null’
 from INFORMATION_SCHEMA.COLUMNS as ic
 left join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE as icu on ic.TABLE_NAME=icu.TABLE_NAME and ic.COLUMN_NAME=icu.COLUMN_NAME
 where ic.TABLE_NAME=‘YourTableName’ and COLUMNPROPERTY(object_id(ic.table_name),ic.column_name,‘IsIdentity’)=0
 and icu.TABLE_NAME is null

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Dynamic SELECT statement with SP by querying information_schema.columns in SQL Server 2008/2005

Some days from now, I am getting new requests everyday to solve and I love to solve problems as much as I can and help community more and more.  Situation was like, there is a table which has few fields like Name, Address, Age ,_NO etc. but reader don’t want to return columns starts with “_”.  Very simple thing, right. Suppose name of Table is “DemoTable” you can simply write down
SELECT Name, Address, Age from DemoTable
But requirement doesn’t ends here,  it needs to be dynamic and should work with any table without even specifying the column name. Here we have to start thinking something different, let me show you how I did that? Have a look at below script and enjoy SQL Programming!!!!
–Demo table
create table DemoTable
(
Name varchar(10),
Address varchar(10),
Age int,
_No int
)

 
–SP which will accept tablename and character of the column name
–which needs to be eliminated from the list of results.
create proc DynamicSelect
@TableName varchar(20),
@FilterChar char(1)
AS
BEGIN
SET NOCOUNT ON
      DECLARE @Cols NVARCHAR(500)
      SET @Cols=
      –gathering column list in @Cols variable by querying Information_Scehma.columns table
      SELECT @Cols=@Cols+ s.column_name + ‘, ‘ FROM
      (select COLUMN_NAME from  information_schema.columns where TABLE_NAME=‘TableA’ and charindex(@FilterChar,COLUMN_NAME)<=0) AS s
      SET @Cols=LEFT(@Cols,LEN(@Cols)-1)
      set @cols=‘SELECT ‘ + @Cols + ‘ From ‘ + @TableName
      EXECUTE sp_executeSQL @Cols
END


–check SP whether it actually works!!!!
Exec DynamicSelect ‘DemoTable’,‘_’
 
Actually above task could be done by CURSOR also but as long as possible I always would like to avoid the cursor.
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Make all columns from all tables in database, NOT NULL and set Default value in SQL Server 2008

Today I helped one person in one forum, he wanted to generate script to make all columns in all tables NOT NULL and wanted to set DEFAULT value 0 for all numeric data types. I felt to share it with all my readers as it could be interesting to know and sometime could be very helpful and handy with some customize changes.
select
not_null = ‘alter table ‘ + table_name + ‘ alter column ‘
                  + column_name + ‘ ‘ + data_type
                  + case when data_type = ‘numeric’ then ‘(‘ else end
                  + case when data_type = ‘numeric’ then convert(varchar,numeric_precision_radix) else end
                  + case when data_type = ‘numeric’ then ‘,’ else end
                  + case when data_type = ‘numeric’ then convert(varchar,numeric_scale) else end
                  + case when data_type = ‘numeric’ then ‘)’ else end
                  + ‘ not null ‘
,default_0 = ‘alter table ‘ + table_name
                  + ‘ add default 0 for ‘ + column_name
from information_schema.columns
where COLUMN_DEFAULT is null
and   data_type not like ‘%char%’
and   data_type not like ‘%time%’
and   data_type not like ‘%image%’
and   data_type not like ‘%binary%’

 

Above query will list the T-SQL script which you can run in your query editor.
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com