Ques 36. How To Get a List of Columns using the "sys.columns" View in MS SQL Server?
If you have an existing table, but you don't remember what are the columns defined in the table, you can use the "sys.columns" system view to get a list of all columns of all tables in the current database.
In order to a list of columns of a single table, you need to join sys.columns and sys.tables as shown in the tutorial example below:
SELECT * FROM sys.columns c, sys.tables t
WHERE c.object_id = t.object_id
AND t.name = 'wb'
GO
object_id name column_id user_type_id max_length
2073058421 id 1 56 4
2073058421 subject 2 167 80
2073058421 description 3 167 256
2073058421 create_date 4 61 8
You can see the column names easily from the sys.columns view. But you can only see the column type IDs. This requires another join to get the column type names. You may try the "sp_columns" stored procedure to get a better list of columns shown in the next tutorial.
Is it helpful?
Add CommentView Comments
Ques 37. How to create new table with "CREATE TABLE" statements?
This is the second tutorial of a quick lesson on creating database objects with Transact-SQL statements. This section shows you how to create a database, create a table in the database, and then access and change the data in the table. Because this section is an introduction to using Transact-SQL, it does not use or describe the many options that are available for these statements. This SQL Guide assumes that you are running SQL Server Management Studio Express.
To create a table, you must provide a name for the table, and the names and data types of each column in the table. It is also a good practice to indicate whether null values are allowed in each column.
Most tables have a primary key, made up of one or more columns of the table. A primary key is always unique. The Database Engine will enforce the restriction that any primary key value cannot be repeated in the table.
Is it helpful?
Add CommentView Comments
Ques 38. How To Get a List of Table Columns using the "sp_columns" Stored Procedure in MS SQL Server?
If you have an existing table, but you don't remember what are the columns defined in the table, you can use the "sp_columns" stored procedure to get a list of all columns of the specified table. The following tutorial script shows you a good example:
sp_columns wb
GO
TABLE_OWNER TABLE_NAME COLUMN_TABLE TYPE_NAME ...
dbo tip id int ...
dbo tip subject varchar ...
dbo tip description varchar ...
dbo tip create_date datetime ...
The "sp_columns" stored procedure returns a long list of properties for each column of the specified table. Take a look at each of them.
Is it helpful?
Add CommentView Comments
Ques 39. How to insert and update data into a table with "INSERT" and "UPDATE" statements?
Now you how to create a database, create a table in the database, and then access and change the data in the table. Because here is an introduction to using Transact-SQL, it does not use or describe the many options that are available for these statements. Now we assumes that you are running SQL Server Management Studio Express.
Now that you have created the Products table, you are ready to insert data into the table by using the INSERT statement. After the data is inserted, you will change the content of a row by using an UPDATE statement. You will use the WHERE clause of the UPDATE statement to restrict the update to a single row. The four statements will enter the following data.
ProductID ProductName Price ProductDescription
1 Clamp 12.48 Workbench clamp
50 Screwdriver 3.17 Flat head
75 Tire Bar Tool for changing tires
3000 3mm Bracket .52
The basic syntax is: INSERT, table name, column list, VALUES, and then a list of the values to be inserted. The two hyphens in front of a line indicate that the line is a comment and the text will be ignored by the compiler. In this case, the comment describes a permissible variation of the syntax.
Is it helpful?
Add CommentView Comments
Ques 40. How To Get a List of Columns using the "sp_help" Stored Procedure in MS SQL Server?
Another way to get a list of columns from a table is to use the "sp_help" stored procedure. "sp_help" returns more than just a list of columns. It returns: the table information, the column information, the identity column, the row GUID column, the primary key, indexes, and constraints. It you run "sp_help tip" in SQL Server Management Studio, you will see the result as shown in this picture: