Definition And Explaination:-
ALTER TABLE allows you to change the structure of an existing table. For example, you can add or delete columns, create indexes, change the type of existing columns, or rename columns or the table itself.You can use ALTER TABLE to modify the structure of a table that has not been added to a database.Alter table command allows
* To add the field;
* To delete the field out;
* To change the DEFAULT value for any of the field;
* To add or delete the table PRIMARY KEY;
* To add or delete the table FOREIGN KEY;
* To add or delete UNIQUE constraint;
* To add or delete CHECK constraint;
* To rename fields or table itself;
* To change field type.

Syntax And Structure:-

ADD [ COLUMN ]   column_definition
|    ADD [ COLUMN ] ( column_definition [,   column_definition ... ] )

|    ADD   table_constraint_definition
|    ADD ( table_constraint_definition [, table_constraint_definition ... ] )

|    ALTER [ COLUMN ] column_name  < SET DEFAULT default_option | DROP DEFAULT>
|    DROP  [ COLUMN ] column_name [ drop_behavior ]
|    DROP PRIMARY KEY
|    DROP INDEX index_name
|    DROP CONSTRAINT IDENT

|    CHANGE [ COLUMN ] old_col_name column_definition
|    MODIFY [ COLUMN ] column_definition
|    RENAME [ AS ] table_name

Examples:-

ALTER TABLE customers ADD CONTACT_PHONE VARCHAR(30)

ALTER TABLE customers ADD COLUMN CONTACT_PHONE VARCHAR(30)

ALTER TABLE customers DROP contact_name

ALTER TABLE Person CHANGE LastName Surnname String ( 40 )

ALTER TABLE Person MODIFY  Surnname String ( 30 )

ALTER TABLE offices ADD CONSTRAINT inregion  FOREIGN KEY ( region ) REFERENCES regions

ALTER TABLE salesreps DROP CONSTRAINT worksin

ALTER TABLE Customer ADD COLUMN Fax2 c(20) NOT NULL

Tags: Mysql, RDBMS/DBMS SQL, SQL Server by admin
No Comments »

There are manby things which are considered as basics in dot net nuke.When you are using host sql window from dnn then most definitely you will need to have ideas of basic sql statements to fetch info from db.Few of them are:-

Listing all tables from DataBase:-

SELECT * FROM information_schema.Tables

Get Stored procedure description and Structure:-

select * from information_schema.routines where routine_name = ‘Stored Procedure Name’

Querry to fetch all tables in a database:

select * from sysobjects where type=’u’

Querry to fetch all procedures in a database:

select * from sysobjects where type=’p’

Display Table Creation And Owner info

sp_help table_name

Display Table structure and columns:-

sp_columns @table_name = ‘table_name‘

this will show all fields from table table_name including data type and length information.

Return a list of objects that can be queried in the current environment

EXEC sp_tables

Return information about the syscolumns table in the Company database

EXEC sp_tables syscolumns, dbo, Company, "'SYSTEM TABLE'"

Altering/modifying cloumn Width :-
alter table table_name ALTER COLUMN column_name varchar(30)

Tags: ASP Dot Net, Dot Net Nuke(DNN), SQL Server by admin
No Comments »