These are some SQL Command which we can use in changing Table and Field according to requirement.
Syntax for fetching design view of a table
Select sys.columns.name as n1, sys.types.name as n2 from sys.columns, sys.types where (object_id=(select object_id from sys.tables where name='Tablename')) and (sys.columns.user_type_id=sys.types.user_type_id);
Syntax for alter command
Alter table tablename add coloumn name datatype (size);
Syntax for alter command modify size
ALTER table tablename alter coloumn columnname datatype (size);
Syntax for autogenerate id using 'vpv/12-13/1' like string and also use of CHARINDEX
SELECT 'VUV/12-13/' + CONVERT(VARCHAR, MAX(SUBSTRING(BILLID, LEN(BILLID) + 1 - CHARINDEX('/', REVERSE(BILLID)) + 1, 30)) + 1) AS newbillno
FROM BillMaster
WHERE (BILLID LIKE 'VUV/12-13/%');
Rename table
SP_RENAME 'Old Table','New Table';
Rename Column
SP_RENAME 'Tablename.Old Column name','New column name','column';
Anonymous User
06-Feb-2013