Is there any stored procedure written in which if i pass a database name it could generate a script for that database.... means script for all tables and all stored procedure...
have you considered powershell as a solution? It comes out-of-the-box with SQL Server 2008.
You may want to look at my entry on this http://www.rajib-bahar.com/rajib/BlogEngine.Web/post/2009/12/08/Combining-SMO-and-Powershell-to-Generate-SQL-Database-Schema.aspx
I want to chage the size of user defined datatype.Data type is also has a lot of refence like pk,fk.
How can we chage the size of database i am not able to drop the previous datatype becuase it uses near 1000 tables.
You should be able to do that using SQL Server Management Studio. You can use the Table Designer to make the changes, visually, but rather than pressing the SAVE button you can generate a Script instead.
You can then run that script on your Test database to check it works OK, and once that is approved you can then run it on the Live database. Backup first!
SQL Server Management Studio will generate a script that drops and recreates any dependenices - such as FK - that are impacted by the change. Although if you make the field smaller you will lose any data that is longer than the new limit.
No one has followed this question yet.