Use the custom function to-------the table's structure by specifying its name

Function: To get the column name and type of the table according to the table name



When we edit the SQL program, we use a number of system-built functions, such as Right (), datalength (), and so on, but when we start the project, if we want to be able to meet some of the specific requirements of the processing, we need to edit the function, As the following example is a function that gets the structure of a table

Types of functions:

1. Scalar function: This type of function is the data value of a single

2. Returns the function of the data set: A table-type data set is transferred, divided into the following two types:

2.1 Lines of data Set function: The content of the function is a select language

2.2 Multiple Syria: The contents of a function are composed of multiple select words

This example uses a multiple Syria function, and its format is:

Create function Function_name

([@parameter_name]: Pass in the parameters)

Returns @return_variable table (returns information about the table)

As

Begin

Function_body

Return

End



Example code:

Create function Tablestructure
(@objname nvarchar)----The name of the table to be traced
----------------------------------------------

--fieldname is the name of the column of the table, type is the type of the column, length is the size of the column, and NULL indicates whether it is allowed to be null

Returns @table table (fieldname varchar (50),
Type varchar (16),
length int,
Nullable varchar (3))
----------------------------------------------
Begin
DECLARE @objid int

---1. sysobjects (ID) that matches the table name (name) from the table

--sysobjects is a data column that contains each of the images created in the data library (conditions, presets, records, rules, storage processes, and so on)
Select @objid =id from sysobjects where id=object_id (@objname)

--syscolumns is a data column that contains each of the data rows in each data sheet and video.

---2. Again from the table syscolumns in accordance with the goal of the identity Code (ID), and is not the purpose of the program

(column name, type, length, whether the column is allowed to be empty)
Insert @table
Select
' FieldName ' =name,
' Type ' =type_name (Xusertype),
' Length ' =convert (int,length),
' Nullable ' =case when isnullable=0 then ' no ' else ' yes '
From syscolumns where id= @objid and number=0 order by colid
Return
End



Liaodaibo